ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ НА ДВУХ ТАБЛИЦАХ MYSQL С 3 СВЯЗАННЫМИ КОЛОННАМИ

Поскольку в вашем вопросе нет примеров данных, я попытался решить эту проблему логически.

Создайте две разные таблицы представления: A, B из daily_posting и TicketsalesDetails с одинаковым именем столбца.
Затем используйте UNION of INNER JOIN (A∩B) and Difference of two table (A-B, B-A) в соответствии с диаграммой ВЕННА ВНЕШНЕГО СОЕДИНЕНИЯ

Итак, ваш запрос должен быть:

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
    )    

Эмулируйте FULL OUTER JOIN следующим образом:

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;

В запросе предполагается, что PNR, Station и Date не равны NULL.

Если эти столбцы могут содержать NULL, используйте

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;

Более простой (и быстрый) способ получить FULL OUTER JOIN — получить желтый и оранжевый цвета с помощью одного SELECT и получить зеленый цвет с помощью второго SELECT:

SELECT ...
    FROM a
    ...
UNION ALL
    SELECT ...
        FROM b
        LEFT JOIN a ON ...
        ...
        WHERE a.id IS NULL

Примечания:

• UNION ALL быстрее, чем UNION или UNION DISTINCT
• Неясно, нужен ли GROUP BY.