How to Link two table with mutliple fields

[kristeljoymalapitan]

Good Day,

Please help the best approach in linking two tables (using left join) with multiple fields, please see below sample query

table1:

LOAD

cust_id,

app_id,

account_number

FROMsource....;

LEFT JOIN(table1)

LOAD

cust_id,

app_id,

reasons

RESIDENT (table2);

I need to link table1 and table2 using cust_id and app_id fields with OR operator

Thank you

[kristeljoymalapitan]

table1:

load autonumber(num(cust_id)&num(app_id)) as %key

left join (table1)

load

autonumber(num(cust_id)&num(app_id)) as %key,

own_buss

resident table2;

Hi ,

above is my sample script and sample data was attached previously.

I need to get own_buss (column) data from table2 using my key indentifier  (cust_id and app_id) on both tables 

Thanks

[HirisH_V7]

Hi you can use something like this,

Data:
LOAD *,
APID&'|'&CUSID as KEY%
 INLINE [
    APID, CUSID, ACnum
    1, 898, 4343
    2, 66, 343142
    3, 5545, 3434344
];
Join
LOAD * INLINE [
    APID, CUSID, Reasons
    1, 898, A
    2, 66, S
    3, 5545, B
    4, 5545, B
] where Exists(KEY%,APID&'|'&CUSID);

[kristeljoymalapitan]

Hi,

what will be the source table for your below sample query,

I need to join different tables with own data, It seems query provided declared the content per column

Please share also the use of INLINE syntax

Thank you

[HirisH_V7]

Please post a sample data and your desired output, out of it.

[kristeljoymalapitan]

table1

cust_id

001,

002

appl_id

001,

002,

003

table2

cust_id

001,

003

appl_id

001,

002

I need to join both tables using cust_id and appl_id as a key identifier then the output should be 01 only since I'am using AND operator

Thank you

[HirisH_V7]

You can do inner Join, it will give only matched records in both.

Still i am not clear, what your'e trying to achieve. 

plot Required out put table, from your table provided.

[kristeljoymalapitan]

table1(cust_id)	table1(appl_id)	table2(cust_id)	table2(appl_id)	table3(reasons)
001	001	001	001	good
002	003	002	002	bad
			003	poor

please see below output, only 001 matches both table, 001 is a sample data content of each table

001

good

[kristeljoymalapitan]

I need to join table1 and table2 using appl_id and cust_id as their identifier (both columns in each tables), synthetic key are encountered since they have two same columns as identifier both tables.

table1 (appl_id,cust_id)

inner join 

table2 (appl_id,cust_id)

In table1  both columns are in character datatype while in table2 both columns are in number datatype reason why i  tried to use  below syntax both but I think it's not working.

text(appl_id & cust_id)   

 autonumber(appl_id & cust_id)    

Please help me on this one

Thank you

[venkat1]

why cant you use link table ?

[venkat1]

please look to attached file,hope this will solve your problem