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屋！