Full Outer Join not working in MySql

Question

I would like to know the alternative for Full Outer Join in MySQL. I know it can be done through union but it's not working because my requirement is little complex I hope so. I have two tables master(branch_id,purchase_mindate,purchase_billvalue) and transfer(branch_id,tra_gtr_date,tra_travalue) in which both id fields will be almost same. Now I need to get the values id,sum(billvalue),sum(travalue) by joining two tables with the conditions

Mindate=current date
Gtrdate=current date
If master's table doesn't have any value but transfer table contains then transfer's value must be included
If master's table contains value but transfer doesn't then master's value must be displayed.

I tried union, join, etc, but nothing helped I know I am doing something wrong somewhere but I can't identify that please help me with this.

I used the below query,

select purchase_master.branch_id, sum(purchase_billvalue) as billvalue, sum(tra_value) as travalue 

from purchase_master 
join purchase_transfer on purchase_master.branch_id=purchase_transfer.branch_id 
where purchase_mindate=CURDATE() and tra_gtr_date=CURDATE() 
group by branch_id

I get values only if two tables contains the current date but I don't get any values if one table has and other doesn't

I have attached Screen shots of tables.

Master Table
Transfer Table

I tried the below query but the result is empty,

select pm.branch_id,pm.purchase_billvalue, pt.tra_value from purchase_master as pm
left outer join purchase_transfer as pt on pm.branch_id=pt.branch_id
where pm.purchase_mindate=CURDATE() and pt.tra_gtr_date=CURDATE()
union
select pm.branch_id,pm.purchase_billvalue, pt.tra_value from purchase_master as pm
right outer join purchase_transfer as pt on pm.branch_id = pt.branch_id
where pm.purchase_mindate=CURDATE() and pt.tra_gtr_date=CURDATE()

I need the result as sum(billvalue) sum(travalue) group by branch_id,

If two tables contains currentdate data
If any one contains the currentdate data

What do you mean by ...both id fields will be almost same? Are they the same or not? — peterm
– peterm, Commented Sep 8, 2013 at 7:24
@Ajeesh Can you post sample data (just a few rows for each table) and desired output? — peterm
– peterm, Commented Sep 8, 2013 at 7:43
@Ajeesh And what is the desired output from this sample data? — peterm
– peterm, Commented Sep 8, 2013 at 7:50

Community · Accepted Answer · 2017-05-23 12:07:02Z

Because both tables more than one row per branch_id, you need to first GROUP BY branch_id - separately in each table - and then emulate the FULL JOIN, either with UNION ALL and one more GROUP BY or with two subqueries, one with LEFT and one with RIGHT outer join.

The 1st (GROUP BY separately, then UNION ALL plus GROUP BY) way:

SELECT branch_id, 
       SUM(purchase_billvalue) AS purchase_billvalue, 
       SUM(tra_value) AS tra_value
FROM
  ( SELECT branch_id, 
           SUM(purchase_billvalue) AS purchase_billvalue,
           NULL AS tra_value
    FROM purchase_master
    WHERE purchase_mindate = CURDATE()
    GROUP BY branch_id
  UNION ALL
    SELECT branch_id, 
           NULL, 
           SUM(tra_value)
    FROM purchase_transfer  
    WHERE tra_gtr_date = CURDATE()
    GROUP BY branch_id
) AS u
GROUP BY branch_id ;

The 3rd way to do this - which may be the most efficient method (but do test!) is provided in @peterm's answer. First do a UNION (not UNION ALL) to find all distinct branch_id from both tables and then do 2 LEFT joins to derived tables where there has been done a GROUP BY branch_id separately.

peterm · Accepted Answer · 2013-09-08 08:59:13Z

2

You can do something like this

SELECT i.branch_id, 
       m.purchase_billvalue billvalue, 
       t.tra_travalue       travalue
  FROM
(
    SELECT branch_id
      FROM master
     WHERE purchase_mindate = CURDATE()
     UNION
    SELECT branch_id
      FROM transfer  
     WHERE tra_gtr_date = CURDATE()
) i LEFT JOIN 
(
  SELECT branch_id, SUM(purchase_billvalue) purchase_billvalue
    FROM master 
   WHERE purchase_mindate = CURDATE()
   GROUP BY branch_id
) m
    ON i.branch_id = m.branch_id LEFT JOIN 
(
  SELECT branch_id, SUM(tra_travalue) tra_travalue
    FROM transfer  
   WHERE tra_gtr_date = CURDATE()
   GROUP BY branch_id
) t
    ON i.branch_id = t.branch_id

Sample output (based on your screenshots and assuming that date values in both tables for all shown rows are equal):

| BRANCH_ID | BILLVALUE | TRAVALUE |
|-----------|-----------|----------|
|         2 |  72580.61 |   119947 |
|         3 |    (null) |     9940 |

Here is SQLFiddle demo

edited Sep 8, 2013 at 8:59

answered Sep 8, 2013 at 7:28

peterm

93k16 gold badges157 silver badges162 bronze badges

7 Comments

darudude Over a year ago

I dont think this satisfies requirements 3 and 4. Ids would have to exist in both tables for this to work.

Ajeesh Over a year ago

@peterm I am getting error like Unknown column 't.tra_travalue' in 'field list' while trying your coding

peterm Over a year ago

It means that there is a typo in your code (e.g. you gave a different (other than t) alias for transfer table but didn't change it in select clause ). Did you look at the working sqlfiddle example that I provided?

Ajeesh Over a year ago

@peterm Please have a look at this fiddle SQL Fiddle, the result was not expected right? there shouldn't be any travalue right?

peterm Over a year ago

@Ajeesh Right. I've updated answer and sqlfiddle. Here is sqlfiddle with your sample data and updated query. Does it work as expected now?

|

Collectives™ on Stack Overflow

Full Outer Join not working in MySql

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related