MySQL COUNT in two tables same time.

Question

Hello!

My question is not so hard, i just really can't find the answer anywhere.

I've got 3 tables.

webshops, webshop_category and webshop_item.

My query is:

SELECT webshops.id, webshops.name, webshops.tax, webshops.contact_name, webshops.contact_email, webshops.contact_phone, webshops.contact_address, COUNT(webshop_category.id), COUNT(webshop_item.id) 
FROM webshops, webshop_category, webshop_item 
WHERE webshops.id = webshop_category.ws_id AND webshop_category.id = webshop_item.ws_category
GROUP BY webshops.id

My #1 webshop got 2 categories with 4 items. But with this query it says:

id  ...  COUNT(webshop_category.id)  COUNT(webshop_item.id)
 1                    4                         4

But i just have 2 categories. So i'd like it to be:

id  ...  COUNT(webshop_category.id)  COUNT(webshop_item.id)
 1                    2                         4

How can i do this?

Thanks for the help.

Answer 1

Use a COUNT(DISTINCT webshop_category.id) that should do it in this case.

Answer 2

put a distinct inside the count like:

SELECT webshops.id, webshops.name, webshops.tax, webshops.contact_name, webshops.contact_email, webshops.contact_phone, webshops.contact_address, COUNT(DISTINCT webshop_category.id), COUNT(webshop_item.id) 
FROM webshops, webshop_category, webshop_item 
WHERE webshops.id = webshop_category.ws_id AND webshop_category.id = webshop_item.ws_category
GROUP BY webshops.id