CODEWITHSUNDEEP
mysqlsql
guyfromfl
guyfromfl

Reputation: 1212

MySQL Select records not related to a certain record

Ok, sorry if this is a repost, I really don't know how to word what I'm looking for to really search for it.

I have 3 tables:

Amenities:
AmenitiesID
Name

resort_amenities
ResortAmenitiesID
ResortID
AmenityID

resorts
ResortID
ResortName
and so on...

I am working on the code that links the amenties to the resort, and saved in resort_amenities.

What I want is to have a jQuery modal dialog come up with all the amenities from the amenities table, that aren't already linked to that resort.

Right now in my ajax call I pass the resort id, to be used in the sql somewhere.

I believe I need a union but have never built one, out side of school years ago.

Thanks

Upvotes: 1

Views: 530

Answers (2)

Marco
Marco

Reputation: 57573

If I understand what you need, you could try:

SELECT * FROM Amenities a
INNER JOIN resort_amenities ra
    ON a.AmenitiesID = ra.AmenityID
WHERE ra.ResortID <> your_resort_id

or

SELECT * FROM Amenities a
LEFT JOIN resort_amenities ra
    ON a.AmenitiesID = ra.AmenityID
WHERE ra.ResortID IS NULL 
   OR ra.ResortID <> your_resort_id

Upvotes: 0

Matt Fenwick
Matt Fenwick

Reputation: 49085

So you want, for a specific resort, the set of amenities not associated with that resort:

SELECT DISTINCT * -- get all those Amenities
FROM Amenities
WHERE NOT EXISTS ( -- that we can't find a counter-example for
  SELECT AmenitiesID 
  From resort_amenities 
  WHERE ResortID = <the resort id>
  AND Amenities.AmenitiesID = resort_amenities.AmenitiesID
)

This is a set substraction, of S = S1 - S2, where S1 is the set of all amenities, and S2 is the set of amenities that are already linked to that resort.

Upvotes: 2

Related Questions