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


Thanks all,

I found the problem, it was due to the fact that the DailyFlowrate field allowed nulls when it should not have allowed them. After I changed that, all now works as expected.

Thanks again for your help