CODEWITHSUNDEEP
sqlpostgresql
don't train ai on me
don't train ai on me

Reputation: 1112

CTE can't be referenced from query?

I'm relatively new to SQL (here it is PostgreSQL) and stuck at a query I've been working on. I created a MWE for it: I get a syntax error at new_years, but I don't see any reason for it:

WITH new_years AS
( 
    SELECT mov_year FROM movie WHERE mov_year > 2000
)
SELECT m.mov_title, m.mov_year
FROM movie m, new_years
WHERE m.mov_year IN new_years;

Then I tried reformatting my query, and also do not understand why the reformatting doesn't work, as new_years can't be found! Why is it that the alias m for movie can be referenced in WHERE, but the alias new_years cannot? Are some aliases different from others?

SELECT m.mov_title, m.mov_year
FROM movie m, (SELECT mov_year FROM movie WHERE mov_year > 2000) AS new_years
WHERE m.mov_year IN new_years;

Thank you very much for the help!

Upvotes: 0

Views: 51

Answers (1)

MatBailie
MatBailie

Reputation: 86706

IN needs to refer to a list of values, not a CTE.

This would be syntactically correct...

WHERE m.mov_year IN (SELECT mov_year FROM new_years);

It would also seem to give gibberish results.


Perhaps you mean...

WITH new_years AS
( 
    SELECT DISTINCT mov_year FROM movie WHERE mov_year > 2000
)
SELECT
  m.mov_title, m.mov_year
FROM
  movie m
INNER JOIN
  new_years
    ON new_years.mov_year = m.mov_year

Or...

WITH new_years AS
( 
    SELECT DISTINCT mov_year FROM movie WHERE mov_year > 2000
)
SELECT
  m.mov_title, m.mov_year
FROM
  movie m
WHERE
  m.mov_year IN (SELECT mov_year FROM new_years);

But then, why not just do this?

SELECT
  m.mov_title, m.mov_year
FROM
  movie m
WHERE
  m.mov_year > 2000

Upvotes: 2

Related Questions