Join two tables on multiple columns in the where clause

[jduluc12]

Hi,

I got two tables.

Table 1:

Item	Num	Desc	Amount
Item1	56	Item_1 desc	100
Item2	98	Item_2 desc	500
Item3	80	Item_3 desc	900

Table 2:

Item	Num	Desc	Amount	ID
Item_1	56	Desc_do_not_match	25	1
Item_2	89	Item_2 desc	89	2
Item_3	100	Desc_do_not_match	450	3
Item_1	456	Desc_do_not_match	123	4
Item_2	145	Desc_do_not_match	98	5
Item_3	165	Desc_do_not_match	45	6

I want to do something like this

left join (Table1)

load Item

resident Table2

where Table1.Num = Table2.Num or Table1.Desc = Table.Desc or Table1.Amount = Table2.Amount * 2;

to have an output like

Item	Num	Desc	Amount	ID
Item1	56	Item_1 desc	100	1
Item2	98	Item_2 desc	500	2
Item3	80	Item_3 desc	900	3

Item1 matches because of Table1.Num = Table2.Num.

Item2  matches because of Table1.Desc = Table2.Desc.

Item3 matches because of Table1.Amount = Table2.Amount * 2.

The above query does not work because it does not recognize the Table1's fields in the where clause

[Saravanan_Desingh · Accepted solution]

Try this,

QUALIFY *;

Table1:
LOAD * INLINE [
    Item, Num, Desc, Amount
    Item1, 56, Item_1 desc, 100
    Item2, 98, Item_2 desc, 500
    Item3, 80, Item_3 desc, 900
];

Join(Table1)

Table2:
LOAD * INLINE [
    Item, Num, Desc, Amount, ID
    Item1, 56, Desc_do_not_match, 25, 1
    Item2, 89, Item_2 desc, 89, 2
    Item3, 100, Desc_do_not_match, 450, 3
    Item1, 456, Desc_do_not_match, 123, 4
    Item2, 145, Desc_do_not_match, 98, 5
    Item3, 165, Desc_do_not_match, 45, 6
];

UNQUALIFY *;

Table3:
NoConcatenate
LOAD *
Resident Table1
Where  Table1.Num = Table2.Num 
	Or Table1.Desc = Table2.Desc 
	Or Table1.Amount = Table2.Amount * 2;
	
Drop Table Table1;

[Saravanan_Desingh]

Output:

[jduluc12]

I was thinking along the same lines.

Thanks for confirming it.