select * from table2
where
exists(select * from table1
where
table1.filed1 = 1 and table2.filed1 = table1.field0 and
table1.filed1 = 2 and table2.filed2 = table1.field0 and
table1.filed1 = 3 and table2.filed3 = table1.field0
)
where
exists(select * from table1
where
table1.filed1 = 1 and table2.filed1 = table1.field0 and
table1.filed1 = 2 and table2.filed2 = table1.field0 and
table1.filed1 = 3 and table2.filed3 = table1.field0
)
http://www.csdn.net/expert/topic/461/461023.shtm
select * from table1 inner join table2 on
case
when table1.field1=1 then table2.field1
when table1.field1=2 then table2.field2
when table1.field1=3 then table2.field3
end =table1.field0Field0 Field1 Field0 Field1 Field2 Field3
---------- ----------- ---------- ---------- ---------- ----------
a 1 A a b c
b 2 A a b c
c 3 A a b c
d 1 B d e f (4 row(s) affected)
inner join (select b1.* from table2 b1 inner join table1 a1 on a1.field1=1 and a1.field0=b1.field1) c1 on b.field0=c1.field0
inner join (select b2.* from table2 b2 inner join table1 a2 on a2.field1=2 and a2.field0=b2.field2) c2 on b.field0=c2.field0
inner join (select b3.* from table2 b3 inner join table1 a3 on a3.field1=3 and a3.field0=b3.field3) c3 on b.field0=c3.field0Field0 Field1 Field2 Field3
---------- ---------- ---------- ----------
A a b c (1 row(s) affected)