Left outer join from more tables with dates

byMR

October 18, 2024

Thank you in advance for your help!

I’ve 4 tables:

A is the main table, with a uniq 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 infos from B, C and D

Example:

Table A

ID_A…infoA and other

1……Tom

Table B

date……ID_A..infoB and other

20241001..1…..20

20241005..1…..30

20241006..1…..40

Table C

date…..ID_A…infoC and other

20241001..1…..x

20241002..1…..y

20241006..1…..z

Table 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

(Although I wrote it in tabular form, the published ones merged, so I put points in the tables)

I can connect these tables with a left outer join, but I can’t connect them so that in the result will be only one date column and the data from the subtables will be in only one row if they belongs to the same date, because the subtables do not contain all the dates.

Many thanks!

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