MySQL relational table record count

Question

I have two tables: clients and subcontractors. Clients has many subcontractors. Subcontractors can have many clients.

My schema goes something like this:

clients
->id
->name
->status

subcontractors
->id
->client_id
->name

My question is how do I count clients with 1 to 2 subcontractors in them?

Thanks. I tried checking this one:

SELECT COUNT across one-to-many relationship

But it seems that this is the reverse of what I am trying to count

Answer 1

select count(*) count from (
  select c.id from clients c
  inner join subcontractors sc on sc.client_id=c.id
  group by c.id
  having count(*) in (1, 2)
) sub;

Answer 2

You should use many to many relation

I've answered because you want to know from me why you should use many to many relation instead of one to many. Because you said

Clients has many subcontractors. Subcontractors can have many clients.

So your both tables should independent and for interaction you can use a map table this called many to many relation. Otherwise you query will more complicated and you need more nested subqueries for simple reason.

I've modified your structure

clients
-> id
-> name
-> status

subcontractors
-> id
-> name

client_subcontractors (this is your many to many relation)
-> id
-> client_id
-> subcontractor_id

And try this:

SELECT COUNT(`client_id`) AS total_client 
FROM `client_subcontractors` 
WHERE `subcontractor_id` IN (1, 2) 
GROUP BY `client_id` 
HAVING COUNT(*) = 2;

Check the SQL FIDDLE DEMO