SQL fetching data when joining different columns depending of the data of one column

Question

I want to get Type, Label1 Label2 and NUM

when Type from TABLE1 = Type Table 2 
AND if type from Table1 = x i want Label1 from table1 = Label from Table2
    If type from table1 = Y I want LAbl~el2 from table1 = Label from table2

ex: If Table1 had Lable1 and Lable2 together in a collunm named Label I would do:

SELECT t1.type, t1.Label, t2.NUM
FROM Table1 t1
     Table2 t2
WHERE t1.type = t2.typ2 and
      t1.LABEL = t2. LABEL

But in my scenario I have two labels in table 1. Label 1 for type X and Label 2 for type Y.

So what's the elegant and efficient way (assuming i have a lot of data in the table) to:

select the data t1.Type, t1.Label1 t1.Label2 and t2.NUM when:
t1.type = t2.type2 
and If t1.type = 'X' -> t1.Label1 = t2.Label
and If t1.type = 'Y' -> t1.label2 = t2.Label?

This should be the result of the select I want to make:

Answer 1

As Abdul suggested, join based on either label1 or label2, based on the value of type; you can use a case statement to do that:

SELECT t1.type, t1.label1, t1.label2, t2.num
FROM table1 t1
JOIN table2 t2
ON t2.type = t1.type
AND t2.label = CASE WHEN t1.type = 'X' THEN t1.label1 ELSE t1.label2 END;

T LABEL1 LABEL2        NUM
- ------ ------ ----------
X DOC                 1000
X INV                 2000
Y        VAL          3000

You could also use decode here, but case is more generic. This is effectively giving you a virtual combined column to join with, which you can visualise by looking just at table1 with that case expression added:

SELECT t1.type, t1.label1, t1.label2,
  CASE WHEN t1.type = 'X' THEN t1.label1 ELSE t1.label2 END AS label
FROM table1 t1;

T LABEL1 LABEL2 LABEL
- ------ ------ -----
X DOC           DOC  
X INV           INV  
X MAT           MAT  
Y        VAL    VAL  
Y        VIE    VIE  
Y        TRU    TRU  

If you are sure that only one of the label columns is ever populated and which one always depends on the flag you could also use coalesce to get the value to join on:

SELECT t1.type, t1.label1, t1.label2, t2.num
FROM table1 t1
JOIN table2 t2
ON t2.type = t1.type
AND t2.label = COALESCE(t1.label1, t1.label2);

With the sample data that gets the same result. But the case approach is probably safer, in case both columns are ever populated.

Answer 2

Join based on condition. (I assume blank values are null)..

SELECT t1.type, t1.Label1, t1.Label2, t2.NUM
FROM Table1 t1,
     Table2 t2
WHERE t1.type = t2.type and
      Isnull(t1.LABEL1,t1.LABEL2)  = t2. LABEL