CODEWITHSUNDEEP
postgresql
pasta_coder
pasta_coder

Reputation: 59

relation "table name" does not exist but it was set in FROM

I'm trying to use a sub-query in the "FROM" section but later get errors "Relation "Table name" does not exist".

I have tried to copy paste my sub-query which works, but creates really long and ugly code, as what I am sending is just a part of the whole thing which represents the same problem.

SELECT Reporter.rid , Reporter.fname , Reporter.lname
FROM Reporter , report , map , keyword , (  SELECT  Reporter.rid                                                             
                                         FROM Reporter , report , map , 
                                              keyword

                                        WHERE (Reporter.rid = report.rid  
                                               AND report.iid = map.iid 

                                                AND map.kword = 
                                                keyword.kword AND 
                                                keyword.subject <> 
                                               'health')     
                                      ) AS nonH

WHERE (Reporter.rid NOT IN(SELECT * FROM nonH) AND Reporter.rid = report.rid)

I would expect this code to work and present me all Reporters who are not linked to anything but 'health'

Error msg is:

ERROR: relation "nonh" does not exist LINE 7: WHERE (Reporter.rid NOT IN(SELECT * FROM nonH) AND Reporter....

Upvotes: 0

Views: 233

Answers (2)

sticky bit
sticky bit

Reputation: 37472

You can't use a derived table in a subquery like that. You either have to reapeat the query for it:

SELECT reporter.rid,
       reporter.fname,
       reporter.lname
       FROM reporter,
            report,
            map,
            keyword,
            (SELECT reporter.rid
                    FROM reporter,
                         report,
                         map,
                         keyword
                         WHERE reporter.rid = report.rid
                               AND report.iid = map.iid
                               AND map.kword = keyword.kword
                               AND keyword.subject <> 'health') AS nonh
       WHERE reporter.rid NOT IN (SELECT reporter.rid
                                         FROM reporter,
                                              report,
                                              map,
                                              keyword
                                              WHERE reporter.rid = report.rid
                                                    AND report.iid = map.iid
                                                    AND map.kword = keyword.kword
                                                    AND keyword.subject <> 'health')
             AND reporter.rid = report.rid);

Or you can use a common table expression:

WITH
nonh
AS
(
SELECT reporter.rid
       FROM reporter,
            report,
            map,
            keyword
            WHERE reporter.rid = report.rid
                  AND report.iid = map.iid
                  AND map.kword = keyword.kword
                  AND keyword.subject <> 'health'
)
SELECT reporter.rid,
       reporter.fname,
       reporter.lname
       FROM reporter,
            report,
            map,
            keyword,
            nonh
       WHERE reporter.rid NOT IN (SELECT rid
                                         FROM nonh)
             AND reporter.rid = report.rid);

That may fix your immediate problem. But to be honest, you query is quite a mess with all that implicit joins of tables which's columns are never used and hard to follow, let alone guess what you might be after. I recommend you rewrite it using explicit INNER JOIN/CROSS JOIN/... syntax. And question yourself what the cross joins are actually good for and if they're really needed.

Upvotes: 1

Chris Curvey
Chris Curvey

Reputation: 10389

there is no table called "nonH". You are creating an "nonH" alias to a subquery in your SELECT clause, but that does not create a persistent object with that name.

Upvotes: 0

Related Questions