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