I have a pair of tables I need to join, I want to return any record that's in tableA, tableB or both. I think I need a FULL OUTER JOIN This query return 1164 records And this one return 3339 records And this one returns 3369 records (so there must be 30 records in tableA that aren't in tableB) I

Full Outer Join failing to return all records from both tables

I have a pair of tables I need to join, I want to return any record that’s in tableA, tableB or both. I think I need a FULL OUTER JOIN

This query return 1164 records

SELECT name FROM tableA 
WHERE reportDay = '2022-Apr-05'

And this one return 3339 records

SELECT name FROM tableB 
WHERE reportDay = '2022-Apr-05'

And this one returns 3369 records (so there must be 30 records in tableA that aren’t in tableB)

select distinct name FROM tableA where reportDay = '2022-Apr-05'
union distinct
select distinct name FROM tableB where reportDay = '2022-Apr-05'

I want to obtain a list of all matching records in either table. The query above returns 3369 records, so a FULL OUTER JOIN should also return 3369 rows (I think). My best effort so far is shown below. It returns 1164 rows and returns what looks to me to be a left join between tableA and tableB.

SELECT tableA.name.*, tableB.name.* 
FROM tableA
FULL OUTER JOIN tableB 
ON (tableA.name = tableB.name and tableB.reportDay = '2022-Apr-05')
WHERE tableA.reportDay = '2022-Apr-05'

Help appreciated. (if this looks question looks familiar, it’s a follow-on question to this one )

UPDATE – Sorry (@forpas) to keep moving the goalposts – I’m trying to match test data to real-data scenario’s.

DROP TABLE tableA;
DROP TABLE tableB;

CREATE TABLE tableA (name         VARCHAR(10),
  reportDay    DATE, 
  val1         INTEGER,
  val2         INTEGER);

CREATE TABLE tableB (name         VARCHAR(10),
  reportDay    DATE, 
  test1        INTEGER,
  test2        INTEGER);

INSERT INTO tableA values  ('A','2022-Apr-05',1,2),  
('B','2022-Apr-05',3,4), ('C','2022-Apr-05',5,6), 
('A','2022-Apr-06',1,2), ('B','2022-Apr-06',3,4), 
('C','2022-Apr-06',5,6), ('Z','2022-Apr-04',5,6), 
('Z','2022-Apr-06',5,6) ;

INSERT INTO tableB values  ('A','2022-Apr-03',5,6), 
('B','2022-Apr-04',11,22), ('B','2022-Apr-05',11,22),  
('C','2022-Apr-05',33,44), ('D','2022-Apr-05',55,66), 
('B','2022-Apr-06',11,22), ('C','2022-Apr-06',33,44), 
('D','2022-Apr-06',55,66), ('Q','2022-Apr-06',5,6);

SELECT tableA.*, tableB.* 
FROM tableA
FULL OUTER JOIN tableB 
ON (tableA.name = tableB.name and tableB.reportDay = '2022-Apr-05'
AND  tableA.reportDay = '2022-Apr-05' )

For this data, I’d hope to see 4 rows of data ‘A’ from tableA only, ‘B’ and ‘C’ from both tables, and ‘D’ from table B only. I’m after the 5th April records only! The query (shown above) suggested by @forpas works except that the ‘A’ record in tableA doesn’t get returned.

UPDATE – FINAL EDIT AND ANSWER!

Ok, the solution seem to be to concetenate the two fields together before joining….

SELECT a.*, b.* 
FROM tableA a FULL OUTER JOIN tableB b 
ON (b.name || b.reportDay) = (a.name || a.reportDay)
WHERE (a.reportDay = '2022-Apr-05' OR a.reportDay IS NULL)
  AND (b.reportDay = '2022-Apr-05' OR b.reportDay IS NULL);

Answer

The condition for the date should be placed in a WHERE clause:

SELECT a.*, b.* 
FROM tableA a FULL OUTER JOIN tableB b 
ON b.name = a.name AND a.reportDay = b.reportDay 
WHERE '2022-Apr-05' IN (a.reportDay, b.reportDay);

or:

SELECT a.*, b.* 
FROM tableA a FULL OUTER JOIN tableB b 
ON b.name = a.name
WHERE (a.reportDay = '2022-Apr-05' OR a.reportDay IS NULL)
  AND (b.reportDay = '2022-Apr-05' OR b.reportDay IS NULL);

See the demo.

Advertisement

Answer