CODEWITHSUNDEEP
sqlpostgresqlsetwith-statementdisjoint-sets
Mysteryxx3
Mysteryxx3

Reputation: 19

Trouble with 'With' statements SQL query

I'm having difficulties writing an SQL query that has multiple WITH statements. I'm quite new to SQL so please bear with me. The context is unimportant but in this case, I'm trying to query a movie database as part of an assignment. This particular question wants me to find directors' ids who never won a director award who directed the largest number of movies [I'm not as interested in alternative solutions to the question --> keener to learn how to fix my code].

I'm getting an error I don't really understand:

WITH awd AS (
SELECT director.id
FROM director INNER JOIN director_award 
ON director.title = director_award.title 
AND director.production_year = director_award.production_year),

mc AS (
SELECT COUNT(*)
FROM movie)

SELECT director.id
FROM director JOIN movie 
ON director.title = movie.title 
AND director.production_year = movie.production_year
WHERE director.id NOT IN awd AND MAX(mc);

For some reason, I get an error on awd in line 13? Could anyone explain this to me?

(feel free to let me know if I'm not asking my question in the appropriate way! I'm also new to stack overflow)

Upvotes: 1

Views: 61

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270823

A CTE must be treated as a table. So you would seem to want:

WHERE director.id NOT IN (SELECT id FROM awd)

I'm not sure what MAX(mc) is supposed to be, though.

Upvotes: 0

Related Questions