CODEWITHSUNDEEP
sqlsqlite
Steph
Steph

Reputation: 19

Get row that matches all multiple rows from another table

I have 2 tables:

  1. One table contains customer ids and a service id that the customer subscribes to.
  2. The second table contains the service id's and a service description for all types of services.

What I am trying to do is print out all the customer ids from the first table that have at least 5 matching unique services. Here is what I came up with but its super hacky:

select * from customers left join services where customer.serviceid = services.sid group by servicesid having count(servicesid) >= 5;

is there a better way of doing this?

Upvotes: 0

Views: 61

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Assuming that you have a properly formed database, then any value of serviceid should be valid.

If you want matching customers, use group by:

select c.customerid
from customers c
group by c.customerid
having count(servicesid) >= 5;

If there could be duplicates in the customers table, then use count(distinct servicesid) >= 5.

Upvotes: 1

Related Questions