CODEWITHSUNDEEP
mysqlsqlselect
Robert
Robert

Reputation: 138

MySql - how to find records that have 2 specific values ​in a field, and no more

I have a "rel_type" table, with 3 fields: id, id_customer, id_type, I must uniquely get the id_customer that have only values ​​9 and 11 as id_type. So if I have recods that have these two values ​​but have others, they don't have to be extracted.

I have tried in various ways but without results.

For example:

id | id_customer | id_type
--------------------------
1  |     123     |    11
2  |     345     |     9
3  |     123     |     9
4  |     788     |     5
5  |     788     |    11
6  |     788     |     9
7  |     788     |     4

I expect this output: 123, which is the only id_customer that has the two requested values ​​and no more than these.

Upvotes: 0

Views: 551

Answers (2)

forpas
forpas

Reputation: 164089

You can group by id_customer:

select id_customer
from tablename
group by id_customer
having min(id_type) = 9 and max(id_type) = 11 and count(distinct id_type) = 2

Upvotes: 0

Mureinik
Mureinik

Reputation: 311188

I'd group by the id_customer, and then apply two conditions - that the total count is 2 and that the count of records with 9 or 11 is also 2:

SELECT id_customer
FROM   mytable
GROUP BY id_customer
HAVING   COUNT(*) = 2 AND
         COUNT(CASE WHEN id_type IN (9, 11) THEN 1 END) = 2

Upvotes: 1

Related Questions