CODEWITHSUNDEEP
mysqlsqlselect
Matteo Bononi 'peorthyr'
Matteo Bononi 'peorthyr'

Reputation: 2220

SQL - Select multiple users from a table asoiciated with multiple options AKA Relations division

The problem is that I have a table where users have certain options "active" this is a representation of the table:

USER | OPTION
-------------
1    | A
1    | B
1    | C
2    | A
2    | D
2    | E
3    | A
3    | D
3    | M

let's assume we would like to select only those users that have both option A AND D (users 2 and 3 in this case).

I have thought something like nested SELECT, but I would like something that I could build with a loop, in fact OPTION will have about 40 distinct values and this query should be built with a form on the page.

any ideas? unluckily the database "is done like this" and can not be changed (minor changes could be done, like add another field, but I can't change the logic behind)

Upvotes: 1

Views: 2444

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

select only those users that have both option A AND D

It is called relation division, here is one way to do so:

SELECT `user`
FROM tablename 
WHERE `option` in ('A', 'D')
GROUP BY `user`
HAVING COUNT(DISTINCT `option`) = 2;

See it in action here:

Upvotes: 5

Related Questions