Join Where not isnull

[joey_lutes]

I have 2 tables.  the requirement is simple - join the data ONLY where the data matches its counterpart.

Table1:

ID	ITEM	Color	Size
1	Thing1	Red	S
2	Thing2		M
3	Thing3	Blue	S

Table2:

ID	AltITEM	AltColor	AltSize
1	Thing11	Purple	L
2	Thing12	Pink	L
3	Thing13	Blue	XL

Sometimes there won't be a Color in Table 1.  If there isn't, I don't want the Altcolor populated in the FinalTable

FinalTable:

ID	ITEM	Color	Size	AltItem	AltColor	AltSie
1	Thing1	Red	S	Thing11	Purple	L
2	Thing2		M	Thing12		L
3	Thing3	Blue	S	Thing13	Blue	XL

I'm doing something wrong:

Table1:

     Load

          ID, ITEM, Color, Size

    From Source;

Left Join (Table1)

     Load

          ID, AltItem, Altsize

From AltSource;

Left Join (Table1)

     Load

          ID, AltColor

From AltSource

Where not isnull(Color);

Error:  Field 'Color' not found

Help?

Thank you!

[sunny_talwar · Accepted solution]

May be this:

Table1:

Load ID,

     ITEM,

     Color,

     Size

From Source;

Left Join (Table1)

Load ID,

     AltItem,

     Altsize,

     AllColor

From AltSource;

FinalTable:

NoConcatenate

LOAD ID,

     ITEM,

     Color,

     Size,

     AltItem,

     Altsize,

     If(Len(Trim(Color)) > 0, AllColor) as AllColor

Resident Table1;

DROP Table Table1;