Mysql Question posted in
The official MySQL documentation can be found here.

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

Tags:

2

Answers


  1. 0

    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
    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,
        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
    )
    Login or Signup to reply.
  2. 0

    Emulate FULL OUTER JOIN by the next way:

    WITH 
cte1 AS ( {query 1} ),
cte2 AS ( {query 2} ),
cte0 AS ( SELECT PNR, Station, Date FROM cte1
          UNION
          SELECT PNR, Station, Date FROM cte2 )
SELECT PNR, Station, Date, cte1.TsrAmount, cte2.CRAmount
FROM cte0
NATURAL LEFT JOIN cte1
NATURAL LEFT JOIN cte2;

    The query assumes that PNR, Station and Date are not NULL.

    If these columns may contain NULL then use

    WITH 
cte1 AS ( {query 1} ),
cte2 AS ( {query 2} ),
cte0 AS ( SELECT PNR, Station, Date FROM cte1
          UNION
          SELECT PNR, Station, Date FROM cte2 )
SELECT cte0.PNR, cte0.Station, cte0.Date, cte1.TsrAmount, cte2.CRAmount 
FROM cte0
LEFT JOIN cte1 ON cte0.PNR <=> cte1.PNR 
              AND cte0.Station <=> cte1.Station 
              AND cte0.Date <=> cte1.Date
LEFT JOIN cte2 ON cte0.PNR <=> cte2.PNR 
              AND cte0.Station <=> cte2.Station 
              AND cte0.Date <=> cte2.Date;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search