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!!
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 |
+------+-------+------------+----------+------+-------+------------+----------+