Unable to use multiple not in Statements

Question

I am looking to find entries that do not exist within the Practice and Game table however I am unable to use a second not in statement. Is there any other way to do this?

 select VenueID from Venue
 where VenueID not in (select VenueID from Practice)
 and not in (select VenueId from Game)

score 1 · Accepted Answer · answered Dec 12 '16 at 04:42

You need to repeat the NOT IN statement, including the column name:

SELECT VenueID
FROM Venue
WHERE VenueID NOT IN (SELECT VenueID FROM Practice) AND
      VenueID NOT IN (SELECT VenueId FROM Game)
       ^^^ you omitted the second column name

One alternative to using NOT IN would be to do two left joins:

SELECT VenueID
FROM Venue t1
LEFT JOIN Practice t2
    ON t1.VenueID = t2.VenueID
LEFT JOIN Game t3
    ON t1.VenueID = t3.VenueID
WHERE t2.VenueID IS NULL AND
      t3.VenueID IS NULL

score 0 · Answer 2 · answered Dec 12 '16 at 04:42

You're missing the column name in the second not in (each criterion is separate and the syntax is <column> NOT IN <value set>):

select VenueID from Venue
where VenueID not in (select VenueID from Practice)
and VenueID not in (select VenueId from Game)
--  ^^^^^^^

Unable to use multiple not in Statements

2 Answers2