Mysql left join issue with 3 tables not returning all rows from first table

Question

I have below query

select  catid, cat_name, currency, count(is_reporting_category_sales.id) as total_sales, 
sum(total_sales) as total_earning 
from is_category 
  left join is_reporting_category_sales on is_category.catid = is_reporting_category_sales.category_id 
  join is_reporting_order on is_reporting_order.id = is_reporting_category_sales.order_id 
group by catid, cat_name, currency 
ORDER BY `is_category`.`cat_name` ASC

but this is returning only rows that are common in is_category and is_reporting_category_sales, is_reporting_order but I want to fetch all rows from is_category table. And if there is no order for the category then 0 as total_earning and total_sales.

Sadikhasan · Accepted Answer · 2015-12-10 07:18:17Z

1

You have to Use Left Join

left join is_reporting_order on is_reporting_order.id = is_reporting_category_sales.order_id

Instead of

join is_reporting_order on is_reporting_order.id = is_reporting_category_sales.order_id

answered Dec 10, 2015 at 7:18

Sadikhasan

18.6k23 gold badges85 silver badges126 bronze badges

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

Comments

Professor Abronsius · Accepted Answer · 2015-12-10 07:23:40Z

0

Perhaps using left outer joins you might get the results you expect ( had to guess at some of the aliases for columns btw so some of them might be wrong )

select  c.`catid`, c.`cat_name`, `currency`, count(i.`id`) as 'total_sales', sum(`total_sales`) as 'total_earning' 
from `is_category` c
  left outer join `is_reporting_category_sales` i on c.`catid` = i.`category_id`
  left outer join `is_reporting_order` on o.`id` = i.`order_id`
group by c.`catid`, c.`cat_name`, `currency`
order by c.`cat_name` asc;

answered Dec 10, 2015 at 7:23

Professor Abronsius

33.9k5 gold badges36 silver badges49 bronze badges

Collectives™ on Stack Overflow

Mysql left join issue with 3 tables not returning all rows from first table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related