CODEWITHSUNDEEP
mysqlsql
Paul
Paul

Reputation: 671

MySQl return records that match AND/OR against second table values

This is such a basic question but for some reason all my knowledge of SQL has just exit the building. Three basic tables:

Table A
id | name
---------
1 | John
2 | Mike
3 | Henry
4 | Cooper

Table B
id | tag
---------
1 | chocolate
2 | ice cream
3 | cookies

and a table which joins the two:

Table C
id | name_id | tag_id
---------------------
1 | 1 | 2
2 | 1 | 3
3 | 2 | 1
4 | 3 | 2
5 | 3 | 3

I want to find the people who like BOTH chocolate and cookies. The following gives me all the people that like chocolate and cookies, but I only want the people who like both (chocolate AND cookies instead of chocolate OR cookies).

SELECT name FROM tablea a JOIN tablec c ON a.id = c.name_id WHERE c.tag_id IN (1,3)

The following obviously doesn't work:

SELECT name FROM tablea a JOIN tablec c ON a.id = c.name_id WHERE c.tag_id = 1 AND c.tag_id = 3 GROUP BY name

but it's essentially what I want. Also, I'd like to do this without involving the tableb in the JOIN, because where I'm using this there will be various other tables joined in and I don't want to confuse the picture. This has got to be so simple!

Upvotes: 2

Views: 97

Answers (2)

Jose Rui Santos
Jose Rui Santos

Reputation: 15329

The easiest and fastest way is to join the tablec twice, one for chocolates and the other for cookies

select name 
  from tablea a
 inner join tablec chocolates on a.id = chocolates.name_id
 inner join tablec cookies    on a.id = cookies.name_id
 where chocolates.tag_id = 1
   and cookies.tag_id = 3

Upvotes: 2

Marc B
Marc B

Reputation: 360892

One hackish method: 

SELECT name, COUNT(c.tag_id) AS cnt
FROM tablea a
JOIN tablec c ON a.id = c.name_id
WHERE c.tag_id IN (1,3)
GROUP BY name
HAVING cnt = 2

Count up how many preferences each user has (which would be limited to chocolate or cookies via the where clause), and then return only those users which have 2 preferences.

Upvotes: 4

Related Questions