SQL条件连接 [英] sql conditional join
问题描述
我有3张桌子:
table1:table1_col1, table1_col2,table1_col3
table2 : table1_col1, table2_col2,table2_col3
table3 : table1_col1, table3_col2,table3_col3
正如我试图通过命名来解释的那样,table1:table1_col1是一个唯一键,可以引用table2:table1_col1或table3:table1_col1,但不能两者都引用. 我需要在table1,table2,table3之间建立联接:
As I tried to explain by the naming, table1:table1_col1 is a unique key that can reference either table2 : table1_col1 or table3 : table1_col1 but never both. I need to make a join between table1, table2,table3 such that:
join table1 with table2 if table1:table1_col1 = table2 : table1_col1
join table1 with table3 if table1:table1_col1 = table3 : table1_col1
这在sql语法中可能吗?
谢谢.
Is this possible in sql syntax?
Thank you.
推荐答案
我假设您也想要表2和3中的相应列.
I would assume you want the corresponding columns from table's 2 and 3 too.
select
t1.table_col1,
t2.table2_col2,
t2.table2_col3,
t3.table3_col2,
t3.table3_col3
from
table1 t1
left join table2 t2
on t1.table1_col1 = t2.table1_col1
left join table3 t3
on t1.table1_col1 = t3.table1_col1
此外,如果您只想要表2或表3中的列,并且它们是相同的数据类型,则可以使用NVL()函数,例如
Additionally, if you only wanted the columns from respective table 2 or 3, and they were the same data types, you could use the NVL() function, such as
select
t1.table_col1,
nvl( t2.table2_col2, t3.table3_col2 ) as SecondCol,
nvl( t2.table2_col3, t3.table3_col3 ) as ThirdCol
from
table1 t1
left join table2 t2
on t1.table1_col1 = t2.table1_col1
left join table3 t3
on t1.table1_col1 = t3.table1_col1
这篇关于SQL条件连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!