sqlmysqljoinunion

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
    enter image description here

    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
        )