combine queries 2

[password99]

i have two different queries that return different columns except one. I need to combine the results from two queries into one row identified by common column.

e.g.

query 1 returns:
date charges
1/1/05 2000

query 2 returns:
date cash payment check payment
1/1/05 1000 500


how can i combine the above two to get:

date cash payment check payment charges
1/1/05 1000 500 2000


I thought of joining the two queries with date but One query may not have any data for the date in other query.
In other words query 1 may not have a date that is in query 2 and vice-versa.

 

[SQLSister]

HOw about you Union the distinct dates from each tbale and the left join that derived table to the other two tables?

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[vongrunt]

Try this... replace sample UNION "tables" with your actual queries:

select isnull(A.[date], B.[date]), B.[cash payment], B.[check payment], A.charges
from 
(	select '2005-01-01' as [date], 2000.0 as charges union
	select '2005-01-02', 3000.0
) A 
full outer join
(	select '2005-01-01' as [date], 1000.0 as [cash payment], 500.0 as [check payment] union
	select '2005-01-03', 500.0, 700.0
) B on A.[date] = B.[date]
-- order by ...