A is the main table, with a unique ID. B, C and D are tables containing this ID and Dates and other infos.
I have to create a list: A.ID, A.info, a DATE existing in B, C OR D and the other info from B, C and D.
Example:
A
ID_A...infoA and other
1......Tom
B
date......ID_A..infoB and other
20241001..1.....20
20241005..1.....30
20241006..1.....40
C
date.....ID_A...infoC and other
20241001..1.....x
20241002..1.....y
20241006..1.....z
D
date.....ID_A..infoD and other
20241001...1...abc
20241002...1...cde
20241007...1...fgh
I need this result:
ID...infoA...date......infoB...InfoC...infoD
1....Tom....20240101...20.......x......abc
1....Tom....20240102............y......cde
1....Tom....20240105...30................
1....Tom....20240106...40.......z........
1....Tom....20240107...................fgh
I can connect these tables with a left outer join.
How do I connect them so that the result has only one date column and the data from the subtables will be in only one row if they belong to the same date, although the subtables do not contain all the dates?
You should first extract all available dates among the tables B, C and D for a specific ID and then outer join the various table on ID and date.
Something like this:
SELECT a.id,
a.info,
dates.date,
b.info,
c.info,
d.info
FROM a
JOIN (SELECT id,
date
FROM b
UNION
SELECT id,
date
FROM c
UNION
SELECT id,
date
FROM d
) AS dates
ON a.id = dates.id
LEFT OUTER JOIN b
ON a.id = b.id
AND dates.date = b.date
LEFT OUTER JOIN c
ON a.id = c.id
AND dates.date = c.date
LEFT OUTER JOIN d
ON a.id = d.id
AND dates.date = d.date
It's not clear what RDBMS you're using so the syntax might be slightly different.