CODEWITHSUNDEEP
phpmysqlsql
Miguel Navarro
Miguel Navarro

Reputation: 7

Need to show SQL partial empty results

I need to show empty rows for BRANDS too. I mean, there is a third brand not shown in this query, look:

SELECT 
  da_brands.name AS brand_name,
  COUNT(DISTINCT da_deals.id) AS total_deals,
  0 AS total_downloaded_coupons,
  0 AS total_validated_coupons,
  COUNT(da_logs.id) AS total_likes 
FROM
  da_brands,
  da_deals 
  LEFT JOIN da_logs 
    ON da_logs.fk_deal_id = da_deals.id 
    AND da_logs.fk_deal_id = da_deals.id 
    AND da_logs.type = 'deal_like' 
WHERE da_brands.fk_club_id = 6 
  AND da_deals.fk_brand_id = da_brands.id 
  AND da_brands.date <= NOW() 
GROUP BY da_brands.name 
ORDER BY da_brands.name ASC

RESULTS:

brand_name  total_deals total_downloaded_coupons    total_validated_coupons total_likes
Marca2  2   0   0   1
Marca1  9   0   0   4

This conditional is showing only brands within deals but i want all brands...:

        AND da_deals.fk_brand_id = da_brands.id

Any idea what statement should i use?

Thank you so much.!!!

Upvotes: 0

Views: 32

Answers (1)

Jim
Jim

Reputation: 6881

This following line in the WHERE predicate is the problem...

AND da_deals.fk_brand_id = da_brands.id

You need to LEFT JOIN to da_deals, the same way you did to da_logs, and move that line above into the ON statement for the join.

See below...

SELECT 
  da_brands.name AS brand_name,
  COUNT(DISTINCT da_deals.id) AS total_deals,
  0 AS total_downloaded_coupons,
  0 AS total_validated_coupons,
  COUNT(da_logs.id) AS total_likes 
FROM da_brands
LEFT JOIN da_deals 
ON da_brands.id = da_deals.fk_brand_id
LEFT JOIN da_logs 
ON da_logs.fk_deal_id = da_deals.id 
AND da_logs.fk_deal_id = da_deals.id 
AND da_logs.type = 'deal_like' 
WHERE da_brands.fk_club_id = 6 
AND da_brands.date <= NOW() 
GROUP BY da_brands.name 
ORDER BY da_brands.name ASC

Upvotes: 1

Related Questions