I have two tables from different databases, and I need to create a report, where there is need to see discrepancy in data:

Table A:

Table B:

The result should be only rows different in last two columns:

Now we can see where discrepancy is.

I have tried

select * from table_a
minus
select * from table_b

But it seems not the right approach

CodePudding user response：

Just join the tables by columns and conditions from your Where clause mentioned in comments:

WITH 
    A (FLIGHT_DATE, FLIGHT, AC, DEST, ATD, TDN) AS
        (
            Select '14-JAN-22', 150, 'AIRB',   'JFK', '02:45', '1:35' From Dual Union All
            Select '15-JAN-22', 152, 'BOEING', 'MIA', '02:45', '1:38' From Dual Union All
            Select '15-JAN-22', 145, 'AIRB',   'SEA', '02:45', '1:05' From Dual 
        ),
    B (FLIGHT_DATE, FLIGHT, AC, DEST, ATD, TDN) AS
        (
            Select '14-JAN-22', 150, 'AIRB',   'JFK', '02:45', '1:35' From Dual Union All
            Select '15-JAN-22', 152, 'BOEING', 'MIA', '02:39', '1:38' From Dual Union All
            Select '15-JAN-22', 145, 'AIRB',   'SEA', '01:28', '1:05' From Dual 
        )
Select
    A.FLIGHT_DATE, A.FLIGHT, A.AC, A.DEST, B.ATD "ATD_B", B.TDN "TDN_B", A.ATD "ATD_A", A.TDN "TDN_A"
From
    A
Inner Join
    B ON(
            (A.FLIGHT_DATE = B.FLIGHT_DATE And A.FLIGHT = B.FLIGHT And A.AC = B.AC And A.DEST = B.DEST) 
          AND 
            (A.ATD <> B.ATD OR A.TDN <> B.TDN) 
        )

/*    R e s u l t :

FLIGHT_DATE     FLIGHT AC     DEST ATD_B TDN_B ATD_A TDN_A
----------- ---------- ------ ---- ----- ----- ----- -----
15-JAN-22          152 BOEING MIA  02:39 1:38  02:45 1:38  
15-JAN-22          145 AIRB   SEA  01:28 1:05  02:45 1:05 

*/

Regards...

CodePudding user response：

You can - for your sample data - JOIN both tables on the four identic columns and then set a condition that the combination of the two other columns must differ:

SELECT 
a.a_date, a.flight, a.ac, a.dest, 
b.atd AS atd_b, b.tdn AS tdn_b,
a.atd AS atd_a, a.tdn AS tdn_a
FROM
a JOIN b 
ON 
a.a_date = b.b_date
AND a.flight = b.flight
AND a.ac = b.ac
AND a.dest = b.dest
WHERE NOT (a.atd = b.atd AND a.tdn = b.tdn);

Sidenote: The condition in the WHERE clause can of course also be added in the join clause and the where clause can be removed.

But this will do only as long as the data is in the same form as your example.

Let's assume the are two further different entries in both table A and B for flight 145. Then I guess you would expect two rows for that flight, but in real life, it will be four.

You can replicate this behaviour here:

db<>fiddle

That's why people are asking you which rows should be put together. If you want to get two rows only for that case, you need to tell us how. If it's really intended to see all four rows, this query will work.