FULL OUTER JOIN on TWO MYSQL TABLES WITH 3 RELATING COLUMNS

I have two tables I want to compare data from. The query of the first table is below and it gives me 12 rows. ***** 1st Query ************

SELECT distinct a.pnr AS PNR, a.station_code as Station,a.date as Date,
sum(a.allamount) as TsrAmount
from daily_posting a 
Where a.date between '2024-06-19' and '2024-06-19' and a.station_code in ('ABVS','BNIA','ABVH') 
group by a.date,a.station_code,a.pnr

The second query which is similar gives me 17 rows ******* 2nd Query *********

SELECT distinct b.pnr_NO AS PNR, b.sR_code as Station,b.date_OF_ACTION as Date,
ABS(
sum(b.LOC_TAX_AMOUNT + b.LOC_PAX_FARE + b.LOC_SURCHARGE_AMOUNT + b.LOC_SERVICE_FEE_AMOUNT )) 
as CRAmount 
from ticketsalesdetails b
Where b.date_of_action between '2024-06-19' and '2024-06-19' and b.sr_code in ('ABVS','BNIA','ABVH') 
group by b.date_of_action,b.sr_code,b.pnr_no

My Question is I want a full outer join to combine this two tables query and have the Amount column side by side for comaprison purpose. Mysql handles full outer join by doing LEFT JOIN and Use UNION to combine it with RIGHT join. I used the below query to perform my full outer join but the results are not right. It should give me 17 rows. but it is currently giving me 12 rows and the value of the amount is not correct

****** FULL OUTER JOIN *****

SELECT distinct a.pnr AS PNR, a.station_code as Station,a.date as Date,
sum(a.allamount) as TsrAmount, 
ABS(sum(b.LOC_TAX_AMOUNT + b.LOC_PAX_FARE + b.LOC_SURCHARGE_AMOUNT + b.LOC_SERVICE_FEE_AMOUNT )) as CRAmount 
from daily_posting a LEFT JOIN ticketsalesdetails b 
ON (a.pnr,a.DATE,a.station) = (b.PNR_no,b.date_of_action ,b.sr_code)
Where a.date between '2024-06-19' and '2024-06-19' and a.station_code in ('ABVS','BNIA','ABVH') 
group by a.date,a.station_code,a.pnr 

UNION

SELECT distinct a.pnr AS PNR, a.station_code as Station,a.DATE as Date,
sum(a.allamount) as TsrAmount, -1* sum(b.LOC_TAX_AMOUNT + b.LOC_PAX_FARE + b.LOC_SURCHARGE_AMOUNT + b.LOC_SERVICE_FEE_AMOUNT )  as CRAmount 
from daily_posting a RIGHT JOIN ticketsalesdetails b 
ON (a.pnr,a.DATE,a.station) = (b.PNR_no,b.date_of_action ,b.sr_code)
Where a.date between '2024-06-19' and '2024-06-19' and a.station_code in ('ABVS','BNIA','ABVH') 
group by a.date,a.station_code,a.pnr

I did a LEFT JOIN AND RIGHT JOIN AND COMBINED BOTH QUERIES WITH UNION BUT THE OUTPUT IS NOT CORRECT

Solution

As there are no sample data in your question, so, I tried to solve this issue logically.

Create two different view tables, A, B from daily_posting and ticketsalesdetails with same column name.
Then use UNION of INNER JOIN (A∩B) and Difference of two table (A-B, B-A) , according to VENN Diagram of OUTER JOIN

So, you query should be:

WITH t1 AS (
    SELECT distinct a.pnr AS PNR, 
        a.station_code as Station,
        cast(a.date as DATE) as Date,
        sum(a.allamount) as amount
    from daily_posting a 
    Where   a.date between '2024-06-19' and '2024-06-19' 
        and a.station_code in ('ABVS','BNIA','ABVH') 
    group by a.date,a.station_code,a.pnr 
), t2 AS (
    SELECT distinct 
        b.pnr_NO AS PNR, 
        b.sR_code as Station,
        cast(b.date_OF_ACTION AS Date) as Date,
        ABS(sum(b.LOC_TAX_AMOUNT + b.LOC_PAX_FARE + b.LOC_SURCHARGE_AMOUNT + b.LOC_SERVICE_FEE_AMOUNT )) as amount 
    from ticketsalesdetails b
    Where b.date_of_action between '2024-06-19' and '2024-06-19' 
        and b.sr_code in ('ABVS','BNIA','ABVH') 
    group by b.date_of_action,b.sr_code,b.pnr_no
)

    SELECT t1.PNR,
        t1.Station,
        t1.DATE,
        t1.amount as TsrAmount, 
        t2.amount as CRAmount
    FROM t1 INNER JOIN t2
    ON      t1.PNR = t2.PNR 
        and t1.Station = t2.Station
        and t1.DATE = t2.DATE

UNION ALL 

    SELECT t1.PNR,
        t1.Station,
        t1.DATE,
        t1.amount as TsrAmount,
        '' as CRAmount
    FROM t1 
    WHERE NOT EXISTS (
        SELECT * FROM t2 
        where t1.PNR = t2.PNR 
            and t1.Station = t2.Station
            and t1.DATE = t2.DATE
    )

UNION ALL

    SELECT t2.PNR,
        t2.Station,
        t2.DATE,
        '' as TsrAmount,
        t2.amount as CRAmount
    FROM t2 
    WHERE NOT EXISTS (
        SELECT * FROM t1 
        where t2.PNR = t1.PNR 
            and t2.Station = t1.Station
            and t2.DATE = t1.DATE
    )