[SOLVED] complicated query! need help.

[ballhogjoni]

I hope I can explain this correctly. I want to get the number of users for each of the companies in my db. this is how my db is laid out:

 

table companies (cols are comma seperated):

id,name,

 

table clients (cols are comma seperated):

id,name,company_id

 

Now clients table relates to the companies table via the company_id

 

My problem is constructing a query that will tell me the number of clients for each company. What I mean is that I will not have the specific id when running the query. I need this for a report on my site so i can see how many users/clients are from a specific company. I've tried unions, left joins, regular joins and I can't seem to come up with a way to do this with one query.

 

Thanks,

[corbin]

SELECT cl.company_id, COUNT(cl.id) AS clients, co.name FROM clients cl JOIN companies co ON co.company_id = cl.company_id GROUP BY cl.company_id;

 

 

If I understood what you're trying to do correctly.

 

Not very complicated, by the way.  A simple join with a COUNT....

[fenway]

STrictly speaking, you shouldn't have the name field in there....

[ballhogjoni]

WOW, thx. Queries get complicated for me; but now i see how you laid it out, your right not to complicated.