Select query question: How can I filter one-to-many data using JOIN?

[[email protected]]

MySQL version: 5

 

This is very simplified, but briefly, in this example I am matching people to artists they like.  I'm not actually using the `artist` table, it's just there to clarify the problem.  In this data, Joe likes Lady GaGa and Sara likes Pearl Jam.  Nobody likes Rick Astley.

 

Tables:

people                 people_like_artist          artist
+--------+---------+   +-----------+-----------+   +--------+---------------+
| id     | Name    |   | people_id | artist_id |   | id     | Name          |
+--------+---------+   +-----------+-----------+   +--------+---------------+
| 1      | Joe     |   | 1         | 1         |   | 1      | Lady GaGa     |
| 2      | Dave    |   | 1         | 2         |   | 2      | Pearl Jam     |   
| 3      | Simon   |   | 1         | 3         |   | 3      | Guns 'n Roses |
| 4      | Sara    |   | 2         | 1         |   | 4      | Rick Astley   |
| 5      | Eric    |   | 2         | 2         |   +--------+---------------+
+--------+---------+   | 3         | 3         |
                       | 4         | 2         |
                       +-----------+-----------+

 

 

I have 3 problems I need to solve.  I have the first two done but I really need help with the third.  Any advice on better ways to solve the first two problems is also greatly appreciated.

 

Problem 1 solved.  Find people who like Lady Gaga OR Guns 'n Roses:

SELECT people.id, people.name
FROM people
LEFT JOIN people_like_artist ON (people.id = people_like_artist.people_id AND people_like_artist.artist_id IN (1,3))
GROUP BY people.id;
+--------+---------+
| id     | Name    |
+--------+---------+
| 1      | Joe     |
| 2      | Dave    |
| 3      | Simon   |
+--------+---------+

 

Problem 2 solved.  Find people who DO NOT like Lady Gaga OR Guns 'n Roses:

SELECT people.id, people.name
FROM people
LEFT JOIN people_like_artist ON (people.id = people_like_artist.people_id AND people_like_artist.artist_id IN (1,3))
WHERE people_like_artist.people_id IS NULL
GROUP BY people.id;
+--------+---------+
| id     | Name    |
+--------+---------+
| 4      | Sara    |
| 5      | Eric    |
+--------+---------+

 

Problem 3. Find people who like Lady Gaga AND Guns 'n Roses.  For the life of me I can't figure this out, I've tried everything I can think of and spent far too much time googling.  Here is the data that matches the filter requirement:

+--------+---------+
| id     | Name    |
+--------+---------+
| 1      | Joe     |
+--------+---------+

 

I can't find the answer to this anywhere but there must be a standard way of doing exactly this.  If someone could point me to it I would be truly grateful!!

 

 

 

 

[[email protected]]

I found a solution

 

SELECT people.id, people.name
FROM people
    INNER JOIN people_like_artist ON (
        people.id = people_like_artist.people_id
        AND people_like_artist.artist_id IN (1,3)
    )
GROUP BY people.id
HAVING COUNT(people_like_artist.people_id) = 2; -- number of artists in the join condition

 

http://forums.devshed.com/mysql-help-4/select-query-question-can-i-filter-this-one-to-many-data-using-731699.html

[fenway]

That's not particularly efficient -- a cross join, one for each condition, would be preferable.