CODEWITHSUNDEEP
sqlsqlite
Amira
Amira

Reputation: 15

How to correct the error in this sql code?

select title
from Movie M , Rating R
where exists((select M.mID
               from Movie)
               except (select R.mID
                       from Rating));

Error: near "(": syntax error

Upvotes: 0

Views: 40

Answers (2)

CL.
CL.

Reputation: 180270

Parentheses are used for subqueries, but a compound query is not composed of subqueries, so you must write both SELECTs together:

SELECT title
FROM Movie M , Rating R
WHERE EXISTS (SELECT M.mID
              FROM Movie
              EXCEPT
              SELECT R.mID
              FROM Rating);

But while this query is syntactically valid, it still does not make sense. EXISTS just checks whether the subquery on the rights side return any rows; this usually requires a correlated subquery to make the subquery depend on the current row in the outer query. And it does not make sense to have the Rating table in the outer query.

You should use IN instead of EXISTS:

SELECT title
FROM Movie
WHERE mID IN (SELECT mID
              FROM Movie
              EXCEPT
              SELECT mID
              FROM Rating);

And you already know that all IDs in the Movie table exist in the Movie table, so you do not have to repeat it in the subquery; simply reverse the comparison:

SELECT title
FROM Movie
WHERE mID NOT IN (SELECT mID
                  FROM Rating);

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Do the proper JOINs with LEFT OUTER JOIN

SELECT m.* FROM Movie m
LEFT OUTER JOIN Rating r
                  ON r.mID = m.mID 
WHERE r.mID IS NULL

Upvotes: 1

Related Questions