How to correctly group output from multiple tables

join;

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   |

Best Answer

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.