How to write query for following output in MySQL

Question

Requirements: I want to show the stock for particular B_Name, if date are same it will show on same row, if date are not same it will show on different row and also '-' will be specify

Two tables namely gr and frosted

table gr:
B_Name  B_Date         B_Qty   
A       2015-08-11     15000    
A       2015-08-15     25000      
A       2015-08-31     20000

table frosted:

M_Name       M_Date       M_Qty   
    A       2015-08-11    15000    
    A       2015-08-25    25000      
    B       2015-08-20    20000 
    A       2015-08-15    15000

Following output:

             for Particular B_Name like here (A)
     Date        Inward(B_Qty) Outward(M_Qty)
    2015-08-11     15000      15000
    2015-08-15     25000      15000
    2015-08-31     20000        -
    2015-08-25      -         25000

I tried join and union but it is not working as per my requirements.

Why both MySQL and Oracle tags? Are both products used here? — jarlh
– jarlh, Commented Nov 9, 2015 at 11:11
Whatever rdbms are you using, mysql or oracle, you are looking for LEFT JOIN. — Hotdin Gurning
– Hotdin Gurning, Commented Nov 9, 2015 at 11:12
As @amdixon said, you should use LEFT JOIN because some items from your table frosted may not be existing for the same date as items of the table gr. — Anwar
– Anwar, Commented Nov 9, 2015 at 11:12

Thorsten Kettner · Accepted Answer · 2015-11-09 11:26:52Z

1

You want records from table gr that have no match in table frosted and vice versa. So you need a full outer join, which MySQL doesn't support.

A possible workaround: Get all dates with a union query first, then outer join the tables again.

select 
  alldates.value as "Date",
  coalesce(g.b_qty, '-') as "Inward(B_Qty)",
  coalesce(f.m_qty, '-') as "Outward(B_Qty)"
from
(
  select b_date as value from gr
  where b_name = 'A'
  union 
  select m_date from frosted
  where m_name = 'A'
) all_dates
left join gr g on g.b_date = all_dates.value
               and g.b_name = 'A'
left join frosted f on f.m_date = all_dates.value
                    and f.m_name = 'A'

answered Nov 9, 2015 at 11:26

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

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

1 Comment

Soumya Over a year ago

Thanks for the query .Please correct me, if am i wrong ,here all_dates.value will be gr.B_Date,frosted.M_Date

athosbr99 · Accepted Answer · 2015-11-10 12:19:18Z

0

I have tried and found the required result:

SELECT table1.bdate as "Date",coalesce(t1.B_Qty,'-') as "Inward(B_Qty)",coalesce(t2.M_Qty,'-') as "Outward(M_Qty)" FROM ( select B_date as bdate,B_Name as bname from gr union select M_Date as mdate,M_Name as mname from frosted) as table1 LEFT JOIN gr as t1 ON t1.B_Date = table1.bdate LEFT JOIN frosted as t2 ON t2.M_Date = table1.bdate WHERE table1.bname like 'A'

edited Nov 10, 2015 at 12:19

athosbr99

4932 gold badges7 silver badges24 bronze badges

answered Nov 9, 2015 at 13:20

Bharat Borikar

261 silver badge5 bronze badges

3 Comments

Soumya Over a year ago

Thanks a lot..Just one more question,how to replace the NULL from this symbol '-' or 0

Bharat Borikar Over a year ago

I have updated query to replace NULL to "-", let me know is it useful :-)

Soumya Over a year ago

.Please help me out,this query is working fine but It duplicating the value.

Collectives™ on Stack Overflow

How to write query for following output in MySQL

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related