DSXchange

I have table1(master table) consists of four columns(col1,col2,col3,col4).Another table table 2 is present.I have to join these two tables
based on col1,col2,col3 for few records and col1,col2,col3,col4 for few records,col1,col2 for few records.
currently i am using 3 join stages.Is there any way can i
use one join stage instead of three?because my key columns are not constant and it may be 1,2,3,4 columns.

What are your join rules, how do you determine which 'few records' get which combination of keys?

1)one set of records
col1='A',col2='B' and col3='C'

for the above set i need to join based on 3 three columns

2)another set of records
col1='D',col2='E'
for the above set i need to join based on 2 columns

3)another set of records

col1='e',col2='f' and col3='g' and col4='h'

for the above set i need to join based on 4 columns

Basically create a separate join for each circumstance and develop logic to determine which result to use.

i do not want to use 4 joins.Just want to know can rewrite the logic some other way

C'est impossible (pardon my French).

Create a new column where input columns are concatenated based on your joining criteria. (ABC, DE, EFGH). Use that column as key in your JOIN.

Do it in the source SQL... unless, of course, they don't all live together.