1

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!

Brian Hoover
  • 7,861
  • 2
  • 28
  • 41
Alberto
  • 151
  • 1
  • 8

1 Answers1

1

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

Michael Buen
  • 38,643
  • 9
  • 94
  • 118
  • I suspected that my problem had something to do with LEFT JOIN, and thought reviewing my notes from MySql to look for the right answer, but you answered me before, and I am happy to tell you that you are right, and the query produces the expected results . Thank you! You are a crack! However, I will review my notes before asking next time. Thanks again for your advices! – Alberto May 01 '12 at 08:58
  • Cheers on that, welcome! :-) Try to use Postgresql some time, it's like an opensource Oracle. See what you can achieve with CTE and windowing function for example: http://stackoverflow.com/a/10395130 – Michael Buen May 01 '12 at 09:05
  • After take a closer look to the query, it's itself a master class about LEFT JOIN. I learned more by studying the query than reviewing my notes about MySQL. Thank you very much again! – Alberto May 01 '12 at 09:10