Help with a select query

[yandoos]

Hello i was really hoping for some help with a select query. Before I explain the issue here is the background.

 

I have 3 tables - products, clients, distributions

 

product table

pid

productname

description

price

 

client table

cid

name

age

 

distribution table

pdid

pid*

cid*

status

date

 

I am in the process of building the add distribution form and want to use a select query that will dynamically show the options in a dropdown and  join the 3 tables.

SELECT name, client.cid FROM distribution join product ON distribution.pid = product.pid
                                  join client ON distribution.cid = client.cid

echo '<option value="'.$build['cid'].'">'.$build['name'].'</option>';

Here is the problem... I want ONLY to select the cid and name from the distribution table that has less than 10 records. This is because a client can have only a maximum of 10 distribution records. So I only want these clients to appear as options in the dropdown as opposed to all the clients names and cids. 

 

I really don't know how to achieve this so any help would be ace.

 

Thank you

[Barand]

try

SELECT name, client.cid  
FROM client 
    LEFT JOIN (
	SELECT cid
        FROM distribution
        GROUP BY cid
        HAVING COUNT(*) >= 10
    ) as sub ON client.cid = sub.cid
WHERE sub.cid IS NULL