Joining 2 or more sql queries using outer join

Question

Im trying to return results from 5 sql statement using 'outer join' , i want the stock_id, stock_name and sum of sales each day for the past 5 days to be returned as separate columns. How can I structure it?

select s1.sid as sid,s1.sname as sname, sum1,sum2,sum3,sum4,sum5
from
(
 select stock.id as sid, stock.Name as sname, sum(orders.Quantity) as 'sum1' from orders inner join stock on `Stock_id`= stock.id where  orders.Created_at Like '2020-05-16%' group by Stock_id asc
) 
as s1
full outer join
(
select stock.id as sid,stock.Name as sname, sum(orders.Quantity) as 'sum2' from orders inner join stock on `Stock_id`= stock.id where  orders.Created_at Like '2020-05-17%' group by Stock_id asc
)
as s2 on s1.sid=s2.sid 
outer join
(
select stock.id as sid,stock.Name as sname, sum(orders.Quantity) as 'sum3' from orders inner join stock on `Stock_id`= stock.id where  orders.Created_at Like '2020-05-18%' group by Stock_id asc
) as s3 on s1.sid=s3.sid 
outer join
( 
select stock.id as sid,stock.Name as sname,sum(orders.Quantity) as 'sum4' from orders inner join stock on `Stock_id`= stock.id where  orders.Created_at Like '2020-05-19%' group by Stock_id asc
)as s4 on s1.sid=s4.sid 
outer join
(
select stock.id as sid,stock.Name as sname, sum(orders.Quantity) as 'sum5' from orders inner join stock on `Stock_id`= stock.id where  orders.Created_at Like '2020-05-20%' group by Stock_id asc
)as s5 on s1.sid=s5.sid

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'full outer join ( select stock.id as sid,stock.Name as sname, sum(orders.Quantit' at line 7 — Mariwa
– Mariwa, Commented May 26, 2020 at 15:02
"what if i want it in ascending order?" -- Then use ORDER BY. But you can't use ORDER BY wihtout limiting rows in a subquery. But that's another ball game. — The Impaler
– The Impaler, Commented May 26, 2020 at 15:02

Gordon Linoff · Accepted Answer · 2020-05-26 15:39:52Z

2

Use conditional aggregation:

select s.id as sid, s.Name as sname,
       sum(case when date(o.created_at) = '2020-05-16' then o.Quantity end) as sum1,
       sum(case when date(o.created_at) = '2020-05-17' then o.Quantity end) as sum2,
       sum(case when date(o.created_at) = '2020-05-18' then o.Quantity end) as sum3,
       sum(case when date(o.created_at) = '2020-05-19' then o.Quantity end) as sum4,
       sum(case when date(o.created_at) = '2020-05-20' then o.Quantity end) as sum5
from orders o inner join
     stock s
     on o.Stock_id = s.id 
group by s.id;

This is much simpler and requires no finicky joins or subqueries. Also, it fixes the date arithmetic.

edited May 26, 2020 at 15:39

answered May 26, 2020 at 15:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

6 Comments

The Impaler Over a year ago

In MySQL you can use DATE() instead of TRUNC().

Mariwa Over a year ago

Yes date() works fine. Thanks @TheImpaler and @ Gordon Linoff. This works for me.

Gordon Linoff Over a year ago

@Mariwa . . . MySQL doesn't support full join so your attempted solution wouldn't work anyway.

Mariwa Over a year ago

@Gordon Linoff how would you convert the null values to zero with the query above? i.e. coalesce(column_name,0) as 'name' or ifnull(column_name,0)as 'name'

Mariwa Over a year ago

Question open to anybody.

|

Collectives™ on Stack Overflow

Joining 2 or more sql queries using outer join

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related