MySQL: Get rows with different arguments

Question

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

Mark Byers · Accepted Answer · 2012-09-26 10:35:32Z

4

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.

answered Sep 26, 2012 at 10:35

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

keepwalking Over a year ago

Thanks. I've voted for both answer but i am choosing this because of the fiddle example.

podiluska · Accepted Answer · 2012-09-26 10:35:16Z

3

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

answered Sep 26, 2012 at 10:35

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Collectives™ on Stack Overflow

MySQL: Get rows with different arguments

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related