CODEWITHSUNDEEP
mysqlcountgroup-byisnull
Alberto
Alberto

Reputation: 151

COUNT(), GROUP BY and NULL Values in a MySql query

I have this MySql query working partially:

SELECT p.product_id, p.product_name, p.sales, 
   p.length, p.hits, COUNT(w.product_id) AS favorites 
   FROM `products` AS p, `products_mf_xref` AS m, 
   `wishlist_items`  AS w 
 WHERE m.manufacturer_id = '1' 
   AND p.product_id = m.product_id 
   AND m.product_id = w.product_id 
 GROUP BY m.product_id ORDER BY p.product_id ASC

I'm recovering some fields from a table and trying to get the number of times these products are referenced in another table (this last table was called "whishlist"). The query is working OK, but I only get the products that are at least one time referenced in the wish list table.

I read that count(*) does not get NULL values what make sense, but I need also the products that are not referenced in the wish list table, I mean, products where COUNT(w.product_id) are equal to "0".

Any idea to recover all the products, including the null values? Any idea to change my query? It's going to make me mad!!

Thanks in advance!

Upvotes: 1

Views: 2277

Answers (1)

Michael Buen
Michael Buen

Reputation: 39393

Use LEFT JOIN:

SELECT p.product_id, p.product_name, p.sales, 
   p.length, p.hits, COUNT(w.product_id) AS favorites 
   FROM `products` AS p 
   LEFT JOIN `products_mf_xref` AS m 
       ON p.product_id = m.product_id AND m.manufacturer_id = '1' 
   LEFT JOIN `wishlist_items`  AS w ON m.product_id = w.product_id 
 GROUP BY m.product_id ORDER BY p.product_id ASC

By the way, as much as possible use JOIN to mirror the data relationships, use WHERE for filters

Upvotes: 1

Related Questions