CODEWITHSUNDEEP
mysqlsql
keepwalking
keepwalking

Reputation: 2654

MySQL: Get rows with different arguments

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

Answers (2)

Mark Byers
Mark Byers

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

podiluska
podiluska

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

Related Questions