Join Where not isnull

joey_lutes — Fri, 13 Jan 2017 00:36:41 GMT

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!

Re: Join Where not isnull

sunny_talwar — Fri, 13 Jan 2017 00:41:04 GMT

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;

topic Re: Join Where not isnull in App Development

Join Where not isnull

Re: Join Where not isnull