0

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'
Quasi635
  • 65
  • 2
  • 9

1 Answers1

1

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

Barmar
  • 741,623
  • 53
  • 500
  • 612