CODEWITHSUNDEEP
mysqlsql
oliboon
oliboon

Reputation: 361

Query with multiple IN clause on multiple rows

Say I have this table: 

    | ID | idService |
    | 1  |     5     |
    | 1  |     10    |
    | 2  |     5     |
    | 2  |     15    |
    | 3  |     5     |
    | 3  |     20    |
    | 4  |     5     |
    | 4  |     25    |
I'd like to be able to select all the clients(ID) where the product 5 is present with at least one of the other products (10, 15, 20, 25).

I'm able to do it for one of those combinations with the query:

SELECT ID FROM table WHERE idService IN (5,10) GROUP BY ID HAVING COUNT(DISTINCT idService) = 2

However, if I add the other conditions in this query, I will get all the clients that have at least two of the products, doesn't matter if it's 5 with 10 or 10 with 20.

SELECT ID FROM table WHERE idService IN (5,10) OR idService IN (5,15) OR idService IN (5,20) OR idService IN (5,25) GROUP BY ID HAVING COUNT(DISTINCT idService) = 2

I'm wondering if it's possible to change the IN clauses or replace them to get only the clients with one of the four valid combinations.

Edit:

I've been able to make the query work using a subquery.

SELECT ID FROM table WHERE ID IN ( SELECT ID FROM table WHERE idService =5) AND ( idService =10 OR idService =15 OR idService =20 OR idService =25 ) GROUP BY idSPOrder

Upvotes: 1

Views: 1025

Answers (3)

OlgaMaciaszek
OlgaMaciaszek

Reputation: 3912

select distinct ID from table t1
where (select count(*) from table where ID = t1.ID group by ID) >=2
and (select count(*) from table where Idservice = 5 and ID = t1.ID group by ID) > 0

Upvotes: 3

Pushkar
Pushkar

Reputation: 339

select table.id from table, 
(select id from table where idservice != 5 group by id)t 
where idservice = 5 and table.id = t.id

Upvotes: 0

Aushin
Aushin

Reputation: 1208

select Id 
from Table T
where exists (select 1 from Table where Id = T.Id AND idService = 5)
group by Id
having count(*) >= 2

Upvotes: 1

Related Questions