Derwaan Derwaan - 8 days ago 6
SQL Question

How can I select information where the condition is in multiple rows?

How can I select some information from the

User
table (for example
NAME
and
SURNAME
) where, in the
Option
table, the user I want to select the information having the option
20
and also the option
25
, for example ?

In this example, I want to select the
NAME
and
SURNAME
of user
1
because it's the only one to have both the option
20
and
25
.

Option
table
:

+---------+---------+
| ID_USER | OPTIONS |
+---------+---------+
| 1 | 20 |
+---------+---------+
| 1 | 25 |
+---------+---------+
| 2 | 20 |
+---------+---------+
| 3 | 25 |
+---------+---------+


User
table
:

+---------+---------+----------+
| ID_USER | NAME | SURNAME |
+---------+---------+----------+
| 1 | Jean | Baptiste |
+---------+---------+----------+
| 2 | Henry | Charles |
+---------+---------+----------+
| 3 | Jacques | Fran├žois |
+---------+---------+----------+


The result table:

+------+----------+
| NAME | SURNAME |
+------+----------+
| Jean | Baptiste |
+------+----------+

Answer

Do a JOIN to get the combinations, use WHERE to filter out the expected options. GROUP BY combined with HAVING COUNT DISTINCT to make sure both 20 and 25 values are there.

select u.NAME, u.SURNAME
from User u
join Option o on o.ID_USER = u.ID_USER
where o.options IN (20, 25)
group by u.NAME, u.SURNAME
having count(distinct o.options) = 2

Note: USER is a reserved word in ANSI SQL, so you may need to delimit it as "User".