UNION statment and replavce NULL value with second statement value

[fredong]

I am using a UNION statement and I want to replace the C.Item value from the second statment D.Item if the first C.Item value IS NULL. I also need to group by the First statement SalesID, SalesAmt, Date, and Item without showing the second statement information.Any ideas? Thanks.

--First Statement
select
A.SalesID
A.SalesAmt
B.Date
C.Item --- supposed C.Item have 2 NULL values

From A INNER JOIN B ON A.SalesID = B.SalesID INNER JOIN C ON B.ItemID = C.ItemID

UNION
--Second statement
select
A.SalesID
A.SalesAmt
B.Date
D.Item ---D.CCCC have Orange and Apple values

From A INNER JOIN B ON A.SalesID = B.SalesID INNER JOIN D
ON B.ItemID = D.ItemID

 

[r937]

we will probably need to see example rows of data to illustrate this problem

r937.com | rudy.ca