How to select a column from nested query

user_16883041 0
Viewed 441
SELECT x, createddate, count_ FROM      
 (SELECT *, count(*)
             OVER
                 (PARTITION BY
                 x
                 ) AS count_
            FROM machineslocation_loc AS ml
            JOIN m AS ma ON ma.mid= ml.mid
            JOIN sh AS sh ON sh.shid = ma.shid 
            JOIN ph AS ph ON ph.phid = sh.phid 
            JOIN ar AS ar ON ar.arid = ph.arid 
            JOIN pn AS pa ON pa.pnid = ar.pnid
            
            AND x LIKE '%705N%') tableWithCount

The table machineslocation_loc has createddate column in it. Whenever I call it says "missing FROM-clause entry for table "machineslocation_loc" but x column is coming from that table. I tried ml.createddate and machineslocation_loc.createddate. There are createddate column in other tables too

postgresql
1 Answers

Try calling using the alias of your subquery, and please avoid * , call the name by his name

SELECT x, tableWithCount.createddate, count_ FROM      
 (SELECT x, ml.*,ma.*,sh.*,ph.*,ar.*,pa.*, count(*)
             OVER
                 (PARTITION BY
                 x
                 ) AS count_
            FROM machineslocation_loc AS ml
            JOIN m AS ma ON ma.mid= ml.mid
            JOIN sh AS sh ON sh.shid = ma.shid 
            JOIN ph AS ph ON ph.phid = sh.phid 
            JOIN ar AS ar ON ar.arid = ph.arid 
            JOIN pn AS pa ON pa.pnid = ar.pnid
            
            AND x LIKE '%705N%') as tableWithCount

make sure that not exist another column named createddate from the other tables inside the subquery

UPDATE:but you do not use the rest of the columns on the main query, so remove the * and only put the columns that you need, for example:

SELECT x, tableWithCount.mlcreateddate, count_ FROM      
 (SELECT x,ml.createddate mlcreateddate, count(*)
             OVER
                 (PARTITION BY
                 x
                 ) AS count_
            FROM machineslocation_loc AS ml
            JOIN m AS ma ON ma.mid= ml.mid
            JOIN sh AS sh ON sh.shid = ma.shid 
            JOIN ph AS ph ON ph.phid = sh.phid 
            JOIN ar AS ar ON ar.arid = ph.arid 
            JOIN pn AS pa ON pa.pnid = ar.pnid
            
            AND x LIKE '%705N%') as tableWithCount
user_83083810
Related