To get data in one table that is unmatched in multiple other tables

[RosieGp]

Table1
ColoraddedJan
Red
Yellow
Green

Table2
ColoraddedFeb
Purple
Orange

Table3
ColoraddedMar
White
Black

[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:

ColorName   dateused   colormonth
Yellow      ""         Jan
Green       ""         Jan
Orange      ""         Feb
Black       ""         Mar
Pink        4/3/2017   ""

so far i have this query that does not work correctly:

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.

 

[DaveInIowa]

You have used Table1 three times and haven't referenced Table2 or Table3. Copy/Paste error probably.

 

[RosieGp]

Thanks Dave. yes i made a copy/paste error. my first 3 queries seems to work but 4th query is not returning any data.

 

[simian336]

Rosie,

Just based on what you have given us I think you can do everything you need with just 1 table.

Put all you data in this table.

ComparingTable
Coloradded dateadded dateused

Then you can use the MONTH function or the DATEPART function to give you the month.

Simi

 

[RosieGp]

Thanks I figured out what I was doing wrong in the query. Thanks for all of your help.