Andy,
select c.*,
count(distinct i.PolicyNumber) generalInsurance,
count(distinct p.PolicyNumber) protection
from Clients c
left join GeneralInsurance i
on c.clientId = i.clients_ClientId
left join Protection p
on c.clientId = p.clients_ClientId
group by c.ClientId
having generalInsurance = 0
or protection = 0;
This query states that you want all clients (due to the left joins), but you only want one row per client (group by c.ClientId). Technically, you should list every column from the Clients table in the group by clause since you aren't performing any aggregation on them, but grouping by the primary key guarantees that all the rows returned for each clientId will be the same. So, you can get by with just grouping on the clientId since MySQL will take the first row's values for each client which will be the same as if you had grouped by all the columns in that table. When left joining the two tables, you will get more rows back than you would typically think. So, if you just did a count(i.PolicyNumber), you would get 6 from you sample data instead of 2 (and 6 instead of 3 on your protection count). Notice that 2 times 3 is 6? The distinct inside the count function lets it count each policy only once so you get the right counts. The only thing left is the having clause. A having clause is like a where clause that operates after the grouping is done. The where clause operates before the grouping is done. A distinct difference. Since the grouping is already done, you can use the column aliases (generalInsurance and protection) in the having clause. And it says to skip rows where both of the counts are not zero. So, only show clients who are missing at least one type of coverage.
HTH,
Chad