Joins and restrictions for different columns at the same time

Question

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 ?

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 .

Sample Data:

Table 1:

ID1      ID2
1        2
2        4
3        5

Table 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

Table 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

Expected Outcome

Table 4:

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

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;

Edit your question and provide sample data and desired results. — Gordon Linoff, Oct 11 '16 at 12:25
Why are you asking similar questions over and over again? https://stackoverflow.com/questions/39972398/join-operation-and-restrictions-at-the-same-time, https://stackoverflow.com/questions/39955764/create-table-on-database-based-on-joins-on-other-database-same-server — jarlh, Oct 11 '16 at 12:41
I thought it´s better to start a new question for every new part -> I think this makes it clearer (easy to follow). Sorry if this was not right :/ — AbsoluteBeginner, Oct 11 '16 at 12:48
So you are working with key-value tables. You should avoid them whereever possible, because they are difficult to maintain. How for example would you ensure that each person has at least a name? With a person table and columns name, age, etc., you would simply make name not nullable. Is there any specific reason to store the attributes in key-value pairs? — Thorsten Kettner, Oct 11 '16 at 13:50
BTW: I think it was right to make this a separate question. The other question was about whether top put criteria in `ON` or `WHERE`, this one is about how to join different attributes from a key-value table. — Thorsten Kettner, Oct 11 '16 at 13:54
Yes- but the data (tables etc.) exists in this way and I cannot change the structure (atm): can you plz help me anyway? — AbsoluteBeginner, Oct 11 '16 at 14:10
@AbsoluteBeginner: What more is there to help with? scaisEdge has already given you the answer: you must join tables 2 and 3 once for each attribute. — Thorsten Kettner, Oct 11 '16 at 14:58

score 1 · Answer 1 · answered Oct 11 '16 at 13:24

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;

Joins and restrictions for different columns at the same time

1 Answers1

Linked