MySql Query Join 3 tables with empty rows

Question

I want to list every product in database with visitor tracking and sales info by user id.

Here is what I got so far:
http://www.sqlfiddle.com/#!2/ec506/19

SELECT products.product_name,
       tracking.unique_hits,
       tracking.total_hits,
       COUNT(sales.product_id) as total_sales,
       SUM(sales.amount) as total_revenue
FROM products
INNER JOIN tracking ON products.id = tracking.product_id
INNER JOIN sales ON products.id = sales.product_id
WHERE products.vendor_id = 0;

It outputs this:

|   PRODUCT_NAME | UNIQUE_HITS | TOTAL_HITS | TOTAL_SALES | TOTAL_REVENUE |
|----------------|-------------|------------|-------------|---------------|
| test product 1 |          42 |         52 |           3 |            30 |

But I want it to output products without sales also, so it should output something like this:

|   PRODUCT_NAME | UNIQUE_HITS | TOTAL_HITS | TOTAL_SALES | TOTAL_REVENUE |
|----------------|-------------|------------|-------------|---------------|
| test product 1 |          42 |         52 |           3 |            30 |
| test product 2 |          10 |         13 |           0 |             0 |

Or

|   PRODUCT_NAME | UNIQUE_HITS | TOTAL_HITS | TOTAL_SALES | TOTAL_REVENUE |
|----------------|-------------|------------|-------------|---------------|
| test product 1 |          42 |         52 |           3 |            30 |
| test product 2 |           0 |          0 |           0 |             0 |

In case there is no even visitor tracking data at table.

I have no idea how to do it. Some help needed! :)

blog.codinghorror.com/a-visual-explanation-of-sql-joins

Barmar
– Barmar

2014-04-24 20:00:15 +00:00
Commented Apr 24, 2014 at 20:00 — Barmar
– Barmar, Commented Apr 24, 2014 at 20:00
See Demo

M Khalid Junaid
– M Khalid Junaid

2014-04-24 20:01:02 +00:00
Commented Apr 24, 2014 at 20:01 — M Khalid Junaid
– M Khalid Junaid, Commented Apr 24, 2014 at 20:01

Gordon Linoff · Accepted Answer · 2014-04-24 19:59:20Z

3

Just switch to left outer join:

SELECT products.product_name,
       tracking.unique_hits,
       tracking.total_hits,
       COUNT(sales.product_id) as total_sales,
       coalesce(SUM(sales.amount), 0) as total_revenue
FROM products
LEFT OUTER JOIN tracking ON products.id = tracking.product_id
LEFT OUTER JOIN sales ON products.id = sales.product_id
WHERE products.vendor_id = 0
GROUP BY products.product_name, tracking.unique_hits, tracking.total_hits;

EDIT:

Thanks to M Khalid for the group by. I would write this query with table aliases to make it easier to read:

SELECT p.product_name, t.unique_hits, t.total_hits,
       COUNT(s.product_id) as total_sales,
       coalesce(SUM(s.amount), 0) as total_revenue
FROM products p LEFT OUTER JOIN
     tracking t
     ON p.id = t.product_id LEFT OUTER JOIN
     sales s
     ON p.id = s.product_id
WHERE p.vendor_id = 0
GROUP BY p.product_name, t.unique_hits, t.total_hits;

answered Apr 24, 2014 at 19:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Linoff Over a year ago

@MKhalidJunaid . . . That is a keen observation. I hadn't even realized it was missing.

Collectives™ on Stack Overflow

MySql Query Join 3 tables with empty rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related