CODEWITHSUNDEEP
mysqlexistsnotin
WizardMan
WizardMan

Reputation: 5

Unable to use multiple not in Statements

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)

Upvotes: 0

Views: 704

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521794

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

Upvotes: 1

lc.
lc.

Reputation: 116508

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)
--  ^^^^^^^

Upvotes: 0

Related Questions