Query with multiple IN clause on multiple rows

Question

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

OlgaMaciaszek · Accepted Answer · 2013-09-26 16:27:40Z

3

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

answered Sep 26, 2013 at 16:27

OlgaMaciaszek

3,9201 gold badge32 silver badges33 bronze badges

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

Comments

Aushin · Accepted Answer · 2013-09-26 15:23:35Z

1

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

answered Sep 26, 2013 at 15:23

Aushin

1,2081 gold badge9 silver badges13 bronze badges

3 Comments

oliboon Over a year ago

Won't this give me the clients with the service 5 only? I don't see where I will specify that it needs to be with one of the other 4 services.

Aushin Over a year ago

The Where clause says "get me Ids that have service 5" the group by says "group them together", the "having count(*) > = 2" part says "make sure it has more than 1" which here would mean "5 and at least 1 more". Run it and you should get the correct result.

oliboon Over a year ago

Oh I get it. I should've probably specified that I have other products than the ones I listed above. It would work fine if I only had the products listed in the table. My bad for not being clear.

Pushkar · Accepted Answer · 2013-09-26 16:00:49Z

0

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

edited Sep 26, 2013 at 16:00

answered Sep 26, 2013 at 15:27

Pushkar

3393 silver badges17 bronze badges

1 Comment

Pushkar Over a year ago

It is a join. First table here is 'table' and second table is that nested select query with alias 't'. Join condition is table.id = t.id.

Collectives™ on Stack Overflow

Query with multiple IN clause on multiple rows

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related