Reputation: 2654
Lets suppose i have the next tables
Table names
id | name
1 | mike
2 | john
3 | jack
Table attributes
name_id | skill
1 | css
1 | html
1 | php
2 | html
2 | java
2 | css
3 | java
I need to get all id and names that have, for example, css and html attributes. I've tried using "JOIN" but the number of attributes to search for may be different.
I did not tried anything else yet because i can't figure out what to try.
Thank you
Upvotes: 2
Views: 63
Reputation: 839254
Try using GROUP BY ...
and HAVING COUNT(DISTINCT ...)
:
SELECT name_id
FROM attributes
WHERE skill IN ('css', 'html')
GROUP BY name_id
HAVING COUNT(DISTINCT skill) = 2
See it working online: sqlfiddle
You can join to also get the names.
Upvotes: 4
Reputation: 51514
select names.id, names.name
from
names
inner join
attributes
on names.id = attributes.name_id
where skill in ('css','html')
group by names.id, names.name
having count(distinct skill) = 2 -- where 2 is the number of skills you are looking for
Upvotes: 3