>Hi All,
>
>I have 5 tables all with the same fields, one of which is the date. I want to write a query that will return a single row for each date that contains the values of all the other fields in each table. I have tried using the distinct key word in my select statement, but it only seem to work on a single column.
>
>What I get is multiple rows for each date, with some columns having a null value.
>
>Does anyone have any ides how to accomplish the seeming simple task?
>
>Thanks,
>
>Jim
It would help if you can post some samples.
I think you first need to get all unique dates from all your five tables and then you can use a left join to all your tables again, e.g.
;with cte_Dates as (select Date from Table1 union select Date from tabl2 union)
select C.Date, T1.Field as Field1, T2.Field as Field2, etc. from cte_Dates C LEFT JOIN Table1 T1 on C.Date = T1.Date LEFT JOIN ...
This is assuming you have unique dates in each of your tables (no duplicate dates per table).
If it's not broken, fix it until it is.
My Blog