How to do full outer join to combine two tables in mysql?

Question

I have two tables payroll_advance and payroll_advrtn,and i supposed to do full outer join to get my require result.But,I'm sure full outer join isn't possible in mysql and also i know that full outer join is possible by using the union.But i don't know how can i do join at the following query.

My payroll_advance table produce the following result.

SELECT _id,_tid,_dt,sum(_amount) as _advance FROM payroll_advance WHERE YEAR( _dt )=YEAR(CURDATE()) AND MONTH(_dt) = MONTH(CURDATE()) group by _tid;

+-----+-------+------------+---------+
| _id | _tid  | _dt        | _advance|
+-----+-------+------------+---------+
|  17 | hjg   | 2012-04-18 |    2151 |
|  22 | RKT01 | 2012-04-10 |    2098 |
|  14 | RKT04 | 2012-04-18 |    1511 |
|  16 | RKT09 | 2012-04-09 |     250 |
|  15 | RKT10 | 2012-04-17 |    1313 |
|   8 | RKT21 | 2012-04-03 |    1321 |
|  19 | RKT31 | 2012-04-26 |    2512 |
|  20 | RKT33 | 2012-04-10 |    2250 |
|  25 | T01   | 2012-04-11 |    2500 |
+-----+-------+------------+---------+

And payroll_advrtn gives the following result.

SELECT _id,_tid,_dt,sum(_amount) as _advrtn FROM payroll_advrtn WHERE YEAR( _dt ) = YEAR(CURDATE()) AND MONTH(_dt) = MONTH(CURDATE()) group by _tid;
+-----+-------+------------+---------+
| _id | _tid  | _dt        | _advrtn |
+-----+-------+------------+---------+
|   9 | RKT02 | 2012-04-10 |    2500 |
|   8 | RKT04 | 2012-04-20 |     150 |
+-----+-------+------------+---------+

But i want something like the following result by combining the above two result.

 +------+-------+-------+------------+----------+---------+
| _id  | _tid  | _tid  | _dt        | _advance | _advrtn |
+------+-------+-------+------------+----------+---------+
|   17 | hjg   | NULL  | 2012-04-18 |     2151 |    NULL |
|   22 | RKT01 | NULL  | 2012-04-10 |      999 |    NULL |
|   14 | RKT04 | RKT04 | 2012-04-18 |       11 |     150 |
|   16 | RKT09 | NULL  | 2012-04-09 |      250 |    NULL |
|   15 | RKT10 | NULL  | 2012-04-17 |     1313 |    NULL |
|    8 | RKT21 | NULL  | 2012-04-03 |     1321 |    NULL |
|   19 | RKT31 | NULL  | 2012-04-26 |     2512 |    NULL |
|   20 | RKT33 | NULL  | 2012-04-10 |     2250 |    NULL |
|   25 | T01   | NULL  | 2012-04-11 |     2500 |    NULL |
| NULL | NULL  | RKT02 | NULL       |     NULL |    2500 |
+------+-------+-------+------------+----------+---------+

Any help will be appreciated.Thanks!!

@ypercube: Yes, it doesn't look like anything in particular, but I presume that Op might have made some mistakes while producing the deisred result. I provided an answer giving the FULL OUTER JOIN results. — Anonymous
– Anonymous, Commented Apr 13, 2012 at 8:33

Anonymous · Accepted Answer · 2012-04-13 08:50:15Z

3

In order to do the FULL OUTER JOIN you can do the LEFT OUTER JOIN and UNION with RIGHT OUTER JOIN (~~provided that MySql still does not support FULL OUTER JOIN~~):

select * from A as a
    left outer join B as b on a.col = b.col
union
select * from A as a
    right outer join B as b on a.col = b.col

Note that you can use subqueries for A and B - which should work with your queries. In your case:

select * from (SELECT * FROM t1) as a
    left outer join (SELECT * FROM t2) as b on a._tid = b._tid
union
select * from (SELECT * FROM t1) as a
    right outer join (SELECT * FROM t2) as b on a._tid = b._tid

With result being equal to (provided that I didn't my a mistake in copy-pasting your data):

+------+-------+------------+----------+------+-------+------------+----------+
| _id  | _tid  | _dt        | _advance | _id  | _tid  | _dt        | _advartn |
+------+-------+------------+----------+------+-------+------------+----------+
|   17 | hjg   | 2012-04-18 |     2151 | NULL | NULL  | NULL       |     NULL |
|   22 | RKT01 | 2012-04-10 |     2098 | NULL | NULL  | NULL       |     NULL |
|   14 | RKT04 | 2012-04-18 |     1511 |    8 | RKT04 | 2012-04-20 |      150 |
|   16 | RKT09 | 2012-04-09 |      250 | NULL | NULL  | NULL       |     NULL |
|   15 | RKT10 | 2012-04-17 |     1313 | NULL | NULL  | NULL       |     NULL |
|    8 | RKT21 | 2012-04-03 |     1321 | NULL | NULL  | NULL       |     NULL |
|   19 | RKT31 | 2012-04-26 |     2512 | NULL | NULL  | NULL       |     NULL |
|   20 | RKT33 | 2012-04-10 |     2250 | NULL | NULL  | NULL       |     NULL |
|   25 | T01   | 2012-04-11 |     2500 | NULL | NULL  | NULL       |     NULL |
| NULL | NULL  | NULL       |     NULL |    9 | RKT02 | 2012-04-10 |     2500 |
+------+-------+------------+----------+------+-------+------------+----------+

edited Apr 13, 2012 at 8:50

answered Apr 13, 2012 at 8:00

Anonymous

18.8k2 gold badges42 silver badges65 bronze badges

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

9 Comments

Vijin Paulraj Over a year ago

Thanks for your answer.But,how can i filter the result by date constraint i.e only display the current month's data.

Anonymous Over a year ago

For example add a date restriction (e.g. BETWEEN) to the queries in a WHERE clause.

Vijin Paulraj Over a year ago

When i'm trying to give the field list instead of '*' for the above given query i'm getting the following error "#1052 - Column 'my column name' in field list is ambiguous".Can i get the selected field?

Anonymous Over a year ago

When more than one table is used (in a JOIN for example) there may be two columns with the same name. You need to use aliases, as I did in the answer (e.g. a._tid)

Vijin Paulraj Over a year ago

Done.Thanks @Anonymous for spending your valuable time and solving my problem.marked as accepted answer:)

|

golimar · Accepted Answer · 2012-04-13 08:25:19Z

0

Store the results of both queries like this:

create temporary table a as select ... ;
create temporary table b as select ... ;

Then:

select * from a left join b using (_tid)
union
select * from a right join b using (_tid);

answered Apr 13, 2012 at 8:25

golimar

2,5881 gold badge24 silver badges37 bronze badges

1 Comment

Joe Over a year ago

It's wrong answer. I guess you haven't tested it. It won't work, because the result will be: ERROR 1137 (HY000): Can't reopen table: 'a'. Table a will be locked.

Collectives™ on Stack Overflow

How to do full outer join to combine two tables in mysql?

2 Answers 2

9 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related