How do I correctly group output from multiple tables?

Question

I have more than 2 tables where entries are associated with dates. Each table can have multiple entries per date. I want to produce output that lists a date, then lists the entries of each table for that date. e.g.

date      |   tbl1_col
1/1/2015  |     dat1_1
1/2/2015  |     dat1_2
1/2/2015  |     dat1_3
1/3/2015  |     dat1_4

date      |   tbl2_col
1/1/2015  |     dat2_1
1/2/2015  |     dat2_2
1/2/2015  |     dat2_3
1/3/2015  |     dat2_4

date      |   tbl3_col
1/1/2015  |     dat3_1
1/2/2015  |     dat3_2
1/2/2015  |     dat3_3
1/3/2015  |     dat3_4
1/3/2015  |     dat3_5

I want output like this:

date      |  tbl1_col  |  tbl2_col  | tbl3_col |
1/1/2015  |  dat1_1    |  dat2_1    | dat3_1   |
1/2/2015  |  dat1_2    |  dat2_2    | dat3_2   |
          |  dat1_3    |  dat2_3    | dat3_3   |
1/3/2015  |  dat1_4    |  dat2_4    | dat3_4   |
          |            |            | dat3_5   |

The problem is I'm missing the association between individual table records (I don't know if dat1_1 really corresponds to dat2_1. So the only useful way for me to look it is by date. If I use a join I get output that creates every possibility:

1/2/2015  |  dat1_2    |  dat2_2    | dat3_2   |
1/2/2015  |  dat1_2    |  dat2_2    | dat3_3   |
1/2/2015  |  dat1_2    |  dat2_3    | dat3_2   |
1/2/2015  |  dat1_3    |  dat2_3    | dat3_3   |
1/2/2015  |  dat1_3    |  dat2_3    | dat3_2   |
1/2/2015  |  dat1_3    |  dat2_2    | dat3_3   |

If there is no association between them then you should not expect one query to return any meaningful results. You should run 3 queries and "group" the output the way you want in your application. — jkavalik, Sep 07 '15 at 19:38
As jkavalik states, there is nothing meaningful here so the engine is going to match up every iteration of date unless you somehow force it. Your output is based on a forced relationship on the records based on the suffix of your mock up. It's a lot less meaningful with addresses or random key values or sales figures. — Steve Mangiameli, Sep 09 '15 at 18:12
What DBMS is this for (MySQL, Oracle, PostgreSQL, DB2, Access, SQL Server, ...)? — ypercubeᵀᴹ, Jun 10 '16 at 13:25

score 1 · Answer 1 · edited Jun 10 '16 at 13:40

So from what I can infer, your desired output is a column of all the dates from all 3 tables, with the 3 tables joined onto it.

So something like

Select dates.date, tbl1_col, tbl2.col, tbl3.col from 
    (select distinct Date from table1 
    union
    select distinct date from table2
    union
    select distinct date from table3
    )dates --Now we have a list of all the dates from all the tables.
left join table1 on dates.date=table1.date
left join table2 on dates.date=table2.date
left join table3 on dates.date=table3.date
--we add the tables back on, keyed against the dates.

could be what you are looking for. Just an idea, reading into how you have phrased it.

How do I correctly group output from multiple tables?

1 Answers1