CODEWITHSUNDEEP
mysqlsql
newbie
newbie

Reputation: 21

Finding out only certain Master items from Details table in a Master-Details table relationship

I've been trying to get this going for hours and haven't figured this out yet.

Say I've got 2 tables - master and details.

master/detail has the following data

master table
+------+-------+
| id   | name  |
+------+-------+
|    1 | jeff  |
|    2 | simon |
|    3 | andy  |
|    4 | jerry |
+------+-------+

details table
+----+-----------+---------+
| id | master_id | tag     |
+----+-----------+---------+
|  1 |         1 | WINDOWS |
|  2 |         1 | MAC     |
|  3 |         2 | MAC     |
|  4 |         3 | WINDOWS |
|  5 |         3 | MAC     |
|  6 |         3 | LINUX   |
|  7 |         4 | MAC     |
+----+-----------+---------+

how do I select the master records which has both tags 'WINDOWS', 'MAC'.

So it should only return master_id 1 and 3 which is jeff and andy only.

If I do a 

select distinct(master_id) from details where tag in ('WINDOWS', 'MAC')

it gives me all of them.

Sorry for the newbie question but if anyone can help, it'll be much appreciated.

Upvotes: 0

Views: 77

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You need simple GROUP BY with HAVING clause : 

select master_id
from details
where tag in ('WINDOWS', 'MAC')
group by master_id
having count(*) = 2;

If details table has duplicate tags for master_id then you need count(distinct tag).

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133400

YOu could use a join with count having 2 value only for tag 

select distinct master_id 
from detail 
inner join (
    select  master_id from detail 
    group by master_id 
    having count(distinct tag) = 2 
) t on t.master_id = detail.master_id and detail.tag in ('WINDOWS', 'MAC')

Upvotes: 0

Related Questions