CODEWITHSUNDEEP
mysql
Quasi635
Quasi635

Reputation: 65

Query Many-to-Many relationship to see if one value exists but not another?

I'm trying to query a many to many relationship to see if one tag exists but not another. I'm trying to do this within one simple query.

I've asked a few friends and have checked stack overflow and can't seem to find someone who's trying to do something similar. I setup a SQLFiddle page which shows what I'm trying to do.

http://sqlfiddle.com/#!9/22b741b/8

In the example I have three users: Mike, Alice and George.
I have 7 candy bars: Snickers, Hersheys, Milky Way, KitKat, Reeses, Twizzlers and Sour Patch.
Mike likes Snickers, KitKat, Reeses and Twizzlers.
Alice likes KitKat, Hersheys and Sour Patch.
George likes KitKat and Twizzlers.

I want to find out who likes Twizzlers but not Snickers.

This is the code I have so far. With this I get back Mike and George. I only want George. How can I modify the query to give me the results I'm looking for?

SELECT Users.firstname, Candy.candyname
FROM Users
INNER JOIN UsersCandy ON Users.id = UsersCandy.user_id
INNER JOIN Candy ON UsersCandy.candy_id = Candy.id
WHERE Candy.candyname = 'Twizzlers'
AND Candy.candyname != 'Snickers'

Upvotes: 0

Views: 26

Answers (1)

Barmar
Barmar

Reputation: 780879

Your query is testing candyname from the same row. If the candy is Twizzlers, then it's obviously not also Snickers. So your query just finds everyone who likes Twizzlers.

Write a subquery that finds all the users who like Snickers. Then join that with the query that finds users who like Twizzlers. The second join must be a LEFT JOIN, so you can test for NULL to find a non-matching row, as in Return row only if value doesn't exist.

SELECT DISTINCT u.firstname
FROM Users AS u
JOIN UsersCandy AS uc1 ON u.id = uc1.user_id
JOIN Candy AS c1 ON uc1.candy_id = c1.id
LEFT JOIN (
  SELECT u.id
  FROM Users AS u
  JOIN UsersCandy AS uc2 ON u.id = uc2.user_id
  JOIN Candy AS c2 ON uc2.candy_id = c2.id
  WHERE c2.candyname = 'Snickers'
) AS u2
  ON u.id = u2.id
WHERE c1.candyname = 'Twizzlers' AND u2.id IS NULL

DEMO

Upvotes: 1

Related Questions