Table1
ColoraddedJan
Red
Yellow
Green
Table2
ColoraddedFeb
Purple
Orange
Table3
ColoraddedMar
White
Black
I need the following result set from the tables above:
so far i have this query that does not work correctly:
Please help. thanks.
ColoraddedJan
Red
Yellow
Green
Table2
ColoraddedFeb
Purple
Orange
Table3
ColoraddedMar
White
Black
Code:
[b]ComparingTable[/b]
Coloradded dateadded dateused
Red 1/1/2017 3/1/2017
White 2/4/2017 4/4/2017
Purple 1/1/2017 5/1/2017
Pink 2/3/2017 4/3/2017
I need the following result set from the tables above:
Code:
ColorName dateused colormonth
Yellow "" Jan
Green "" Jan
Orange "" Feb
Black "" Mar
Pink 4/3/2017 ""
Code:
Select ColoraddedJan as ColorName, '' as dateused, 'Jan' as colormonth
from Table1
where ColoraddedJan not in (select Coloradded from ComparingTable)
union
Select ColoraddedFeb as ColorName, '' as dateused, 'Feb' as colormonth
from Table2
where ColoraddedFeb not in (select Coloradded from ComparingTable)
union
Select ColoraddedMar as ColorName, '' as dateused, 'Mar' as colormonth
from Table3
where ColoraddedMar not in (select Coloradded from ComparingTable)
union
Select Coloradded as ColorName, dateused, '' as colormonth
from ComparingTable
where (Coloradded not in (select ColoraddedJan from Table1)
and Coloradded not in (select ColoraddedFeb from Table2)
and Coloradded not in (select ColoraddedMar from Table3))
Please help. thanks.