find all entities by nested list that don’t have some value

I have tables NighClub, Visitor and Visitors_NighClub (many to many). I need to get all night clubs which DONT HAVE certain Visitor.

To get NightClubs which HAVE certain Visitor I do like this:

SELECT nc.id, nc.name
  FROM night_club as nc
    INNER JOIN visitor_night_clubs as vnc ON nc.id = vnc.night_clubs_id 
    WHERE vnc.visitors_id = 3;

But how to get all nightClubs that DONT HAVE some certain Visitor. I cant do like:

WHERE vnc.visitors_id != 3;

Answer

Figure this out

SELECT *
    FROM night_club 
    WHERE night_club.id NOT IN 
(SELECT nc.id
  FROM night_club as nc
    INNER JOIN visitor_night_clubs as vnc ON nc.id = vnc.night_clubs_id 
    WHERE vnc.visitors_id = 4)

Advertisement

Answer