I have 3 tables:
Table 1
id | email ---+---------- 1 | a@a.com 2 | b@a.com 3 | c@a.com 4 | d@a.com
Table 2
order_id | email ---------+-------------- 1 | a@a.com 2 | a@a.com 3 | a@a.com 4 | c@a.com 5 | c@a.com 6 | b@a.com 7 | b@a.com
Table 3
order_id | sku | qty ---------+----------+----- 1 | sku1 | 1 2 | sku1 | 2 3 | sku1 | 1 4 | sku2 | 3 5 | sku2 | 2 6 | sku2 | 6 7 | sku3 | 5
I want to join table 1 to table 2 and table 3.
Get all rows of table 1 and table 3 sku1 + count qty by sku1 with email + count order of customer has ‘sku1’
I want get a result like this:
id | email | sku | order | orderqty ---+----------+--------+----------+-------------- 1 | a@a.com | sku1 | 3 | 4 2 | b@a.com | sku1 | 3 | 5 3 | c@a.com | sku1 | 1 | 6 4 | d@a.com | sku1 | null | null
Can someone help me? Thanks all <3
Advertisement
Answer
I think you want left joins and aggregation:
select t1.id, t1.email, 'sku1' sku, count(distinct t2.order_id) cnt_order, sum(t3.qty) orderqty from table1 t1 left join table2 t2 on t2.email = t1.email left join table3 t3 on t3.order_id = t2.order_id and t3.sku = 'sku1' group by t1.id, t1.email