Left join and possible break down by date group

March 17, 2023

I have 2 tables (Table_1 and Table_2) as below

A1  A2      A3       A4         A5
5   MARS    200.00   Skids      3/1/2023
4   MARS    0        Each       3/1/2023
3   MARS    0        Each       3/1/2023
2   MARS    0        Each       3/1/2023
1   MARS    0        Skids      3/1/2023
5   MARS    0        Each       2/1/2023
4   MARS    0        Skids      2/1/2023
3   MARS    0        Each       2/1/2023
2   MARS    196.5    Skids      2/1/2023
1   MARS    0        Each       2/1/2023

and

B1  B2  B3
1   77  3/1/2023
2   0   3/1/2023
2   0   2/1/2023
1   76  2/1/2023
1   0   1/1/2023
2   0   1/1/2023
1   0   1/1/2023
3   0   1/1/2023

My goal is to use LEFT JOIN to insert row when A1 match with B1 and A5 match with B3 and if not match then NULL. I expect the result below

A1      A2      A3       A4      A5         B1      B2      B3
5       MARS    200.00  Skids   3/1/2023    NULL    NULL    NULL
4       MARS    0       Each    3/1/2023    NULL    NULL    NULL
3       MARS    0       Each    3/1/2023    NULL    NULL    NULL
2       MARS    0       Each    3/1/2023    2       0       3/1/2023
1       MARS    0       Skids   3/1/2023    1       77      3/1/2023
5       MARS    0       Each    2/1/2023    NULL    NULL    NULL
4       MARS    0       Skids   2/1/2023    NULL    NULL    NULL
3       MARS    0       Each    2/1/2023    NULL    NULL    NULL
2       MARS    196.50  Skids   2/1/2023    2       0       2/1/2023
1       MARS    0       Each    2/1/2023    1       76      2/1/2023
NULL    NULL    NULL    NULL    NULL        1       0       1/1/2023
NULL    NULL    NULL    NULL    NULL        3       0       1/1/2023

My query

select a.A1, a.A2, a.A3, a.A4, a,A5, b.B1, b,B2, b,B3
from Table_1 a
left join Table_2 B on a.A1 = b.B1
group by a.A5

It does not work as I expect. Look like I got duplicate and not break down by group.

Can some experts can get the right direction. Thank you.

>Solution :

You need a full outer join here:

SELECT a.A1, a.A2, a.A3, a.A4, a,A5, b.B1, b,B2, b,B3
FROM Table_1 a
FULL OUTER JOIN Table_2 B
    ON a.A1 = b.B1 AND a.A5 = b.B3;

If you happen to be using a database which does not directly support full outer join (such as MySQL), you may simulate as the union of a left and right join:

SELECT a.A1, a.A2, a.A3, a.A4, a,A5, b.B1, b,B2, b,B3
FROM Table_1 a
LEFT JOIN Table_2 B
    ON a.A1 = b.B1 AND a.A5 = b.B3
UNION ALL
SELECT a.A1, a.A2, a.A3, a.A4, a,A5, b.B1, b,B2, b,B3
FROM Table_1 a
RIGHT JOIN Table_2 B
    ON a.A1 = b.B1 AND a.A5 = b.B3
WHERE a.A1 IS NULL;