sqljoinselectleft-join

Left outer join from more tables with dates


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?


Solution

  • 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.