MYSQL Query Issue - HELP

[pblancher]

What I am trying to do is get a listing of products from the table 'mi_inventor_products' - then select the count of the number of distributors and stores that carry that product

I tried with a LEFT JOIN to join the three tables, didnt work. My last atempt has me putting a subquery in, but now I get an error at the subquery.

So I want to do:

COUNT the Distinct Stores

"COUNT(SELECT DISTINCT mi_store_products.store_id from mi_store_products where mi_store_products.product_id=mi_inventor_products.id) AS store_count"

and

COUNT the Distinct Distributors

"COUNT(SELECT DISTINCT mi_distributor_products.distributor_id from mi_distributor_products where mi_distributor_products.product_id=mi_inventor_products.id) AS dist_count"

And get product name and Id

"SELECT mi_inventor_products.product_name, mi_inventor_products.id FROM mi_inventor_products ORDER BY mi_inventor_products.product_name ASC"

HOW do I execute this as once query so that I get the results of

id|product_name|store_count|dist_count

Keeping in mind that not all products may have a distributor or a store or both.

TIA,

Phil

[Lars_Berg]

select id,product_name,(select count(*) 
from mi_distributor_products 
where mi_distributor_products.product_id=mi_inventor_products.id) as distrib,
(select count(*) 
from mi_store_products 
where mi_store_products.product_id=mi_inventor_products.id) AS store_count
from mi_inventor_products

[pblancher]

Tried this:

select
id,
product_name,
(select count() from mi_distributor_products where mi_distributor_products.product_id=mi_inventor_products.id) as distrib,
(select count() from mi_store_products where mi_store_products.product_id=mi_inventor_products.id) AS store_count
from mi_inventor_products

I get:

AN error occurred near "(select count(*) from mi_distributor_products" check syntax

[Roger_Ramjet]

That's because you are using an Aggregate Function without a GROUP BY clause : ie. count(*)

[pblancher]

I tried a group by and keep getting an error.

select id,product_name,(select count(mi_distributor_products.distributor_id)
from mi_distributor_products
where mi_distributor_products.product_id=mi_inventor_products.id) as distrib,
(select count(mi_store_products.store_id)
from mi_store_products
where mi_store_products.product_id=mi_inventor_products.id) AS store_count
from mi_inventor_products
group by mi_inventor_products.product_name

[Roger_Ramjet]

select id, productname, count(distributer_id) as d, count(store_id) as s
from mi_inventor_products as ti 
left join mi_distributor_products as td on ti.id=td.product_id
left join mi_store_products as ts on ti.id=ts.product_id
group by ti.id

[pblancher]

says MySQL Error : field id is ambiguous...

Where do I need to be more specific

[Roger_Ramjet]

Ah. You use non-unique column names across your tables. Bad idea. Either change them all to things like product_id, store_id, etc, or change my code to 'select ti.id' etc

[Lars_Berg]

That's because you are using an Aggregate Function without a GROUP BY clause : ie. count(*)

It is legitimate to use aggregate functions without a group by clause as long as there are no non-aggregated columns in the select list. (Which is the case in my query.)

Probably pblancher is using an outdated Mysql version that does not support scalar subqueries.

 select t.id, productname, count(distributer_id) as d, count(store_id) as s
from mi_inventor_products as ti 
left join mi_distributor_products as td on ti.id=td.product_id
left join mi_store_products as ts on ti.id=ts.product_id
group by ti.id

This will not get the correct result. For instance, if a product have 2 distributors and 3 stores selling it, both counts will be 6 as you are joining all tables.

If you can't use subqueries you need to do a query for each count.

 select 'distributors', ti.id, productname, count(distributer_id) as d
from mi_inventor_products as ti 
left join mi_distributor_products as td on ti.id=td.product_id
group by ti.id
union
 select 'stores', ti.id, productname,  count(store_id) as s
from mi_inventor_products as ti 
left join mi_store_products as ts on ti.id=ts.product_id
group by ti.id  

union requires 4.0. So if you are on an even older version you have to do two separate selects.