同时对不同列进行联接和限制 [英] Joins and restrictions for different columns at the same time

问题描述

我们可以使用join操作，同时对不同的列进行限制吗?我想基于另一个数据库(在示例中称为 DB2)中的三个表在数据库(在示例中称为 DB1)上创建一个表，其中当另一列中有特定条目时，新表中的某些列填充了条目(在示例中，表 2 的attributeID"列中的4"表示性别"列中的条目，5"表示年龄"列中的条目；表 3 中属性ID"列中的7"表示街道"列中的条目).-> 如果是，那怎么办?

Can we use a join operation and make restrictions for different columns at the same time? I want to create a table on a Database (called DB1 in the example) based on three tables from another database (called DB2 in the example) where some columns in the new table filled with entrys when there´s a specific entry in an other column (in the example a"4" in column "attributeID" at table 2 indicates an entry in column "gender", a "5" indicates an entry in column "age"; a"7" in column "attributeID"at table 3 indicates an entry in column "street"). -> If yes, then how to do ?

两个数据库在同一台服务器上，DBMS 是一样的.ID1和ID2基于DB2中的table1；基于 DB2 中的 table2 的 ID2、attributeID 和值(其中性别或年龄根据 attributeID 中的条目写入)；基于 DB2 中的 table3 的 ID2 属性 ID 和值(当属性 ID 中的条目为 7 时写入街道).

Both databases are on the same server and DBMS is the same. ID1 and ID2 based on table1 in DB2; ID2, attributeID and value (where the gender or age is written depending on entry in attributeID) based on table2 in DB2; ID2 attributeID and value (where the street is written when the entry in the attributeID is a 7)based on table3 in DB2 .

样本数据:

表 1:

ID1      ID2
1        2
2        4
3        5

表 2:

ID2    attributeID    value
2      3              Kahtrin ->an example entry which is not relevant
2      4              miss
2      5              22
4      1              active  ->an example entry which is not relevant
4      4              EMPTY/NULL
4      5              47
5      4              mr
5      5              34
5      6              Hindu ->an example entry which is not relevant

表 3

 ID2    attributeID    value
2      5              20% ->an example entry which is not relevant
2      7              middlestreet 40
4      4              chinese ->an example entry which is not relevant
4      7              churchstreet 12
5      3              3Euro
5      7              EMPTY/NULL

预期结果

表 4:

ID1    ID2    gender    age     street
1      2      miss      22      middlestreet 40
2      4                47      churchstreet 12
3      5      mr        34

这是我尝试过的(从我使用 DB1 的角度来看):

Here´s what I tried out (Made from point of view that I´m using DB1):

INSERT INTO table4 
        (id1, 
         id2, 
         gender,
         age,
         street) 
SELECT t1.id1, 
   t1.id2, 
   t2.value
   t2.value
   t3.value
FROM db1.table1 t1 
   LEFT JOIN db1.table2 t2 
          ON t1.ID2=t2.ID2 
             AND value = 4 OR 5
   LEFT JOIN db1.table3 t3 
          ON t1.ID2=t3.ID2 
             AND value = 7;

推荐答案

你的选择是错误的(没有产生预期的结果)你应该使用两次表 t2 使用别名
并且您应该将表 t3 显式分配给值

You select is wrong (don't produce the expected result ) you should use two time the table t2 using an alias
and you should explicit assign the table t3 to value

SELECT t1.id1, 
   t1.id2, 
   t2.value
   t4.value
   t3.value
FROM db1.table1 t1 
   LEFT JOIN db1.table2 t2 
          ON t1.ID2=t2.ID2 
             AND value = 4 
   LEFT JOIN db1.table2 t4 
          ON t1.ID2=t4.ID2 
             AND value = 5             
   LEFT JOIN db1.table3 t3 
          ON t1.ID2=t3.ID2 
             AND t3.value = 7;

这篇关于同时对不同列进行联接和限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！