Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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;