tempestas tempestas - 7 months ago 6
SQL Question

SQL joining a table containing multiple/duplicate values, to both fields on a table that is 1 to 1

Oracle DB

Table1 looks like so, it is strictly one to one, no duplicates...

Row Primary Secondary
--------------------------------
1 1 2
2 3 4
3 5 6


Table2 has the corresponding IDs with multiple names for each.

ID Name
------------------------
1 Server1
2 Server2
3 Server3
3 Server4
3 Server5
4 Server6
4 Server7
5 Server8
6 Server9
6 Server10


Now, this is what I am doing right now, simplified...

Select t1.row row,
t2.name p_name,
t2a.name s_name
From table1 t1
left join table2 t2 on t1.primary = t2.id
left join table2 t2a on t1.secondary = t2a.id


this gives me a result like this, which I know is correct for the code I used..

Row p_name s_name
-------------------------------
1 Server1 Server2
2 Server3 Server6
2 Server3 Server7
2 Server4 Server6
2 Server4 Server7
2 Server5 Server6
2 Server5 Server7
3 Server8 Server9
3 Server8 Server10


What I want, and cannot figure out how to do... is get this result:

Row p_name s_name
-------------------------------
1 Server1 Server2
2 Server3 Server6
2 Server4 Server7
2 Server5 null
3 Server8 Server9
3 null Server10


In essence... I want to be able to pair up values from a 1 to 1 relationship, where there could be 1, 2, 3 or more for each value, but I don't want all the combinations, just the 1, 2 or 3, and then the other set of 1, 2 or 3, and a null value for the numbers are no equal. I am obviously new to SQL and have searched around, I just can't figure out what to try next.

Answer

You need another join conditions that is a sequence number. Fortunately, you can get this using row_number(). This is quite close to what you want:

Select t1.row as row,
       t2.name as p_name,
       t2a.name as s_name
From table1 t1 left join
     (select t2.*, row_number() over (partition by t2.id order by t2.id) as seqnum
      from table2 t2
     ) t2
     on t1.primary = t2.id left join
     (select t2.*, row_number() over (partition by t2.id order by t2.id) as seqnum
      from table2 t2
     ) t2a
      on t1.secondary = t2a.id and t2.seqnum = t2a.seqnum;

Unfortunately, it doesn't handle the situation where the second list is longer than the first. I think this should work:

Select t1.row as row,
       t2.name as p_name,
       t2a.name as s_name
From table1 t1 left join
     (select t2.*, row_number() over (partition by t2.id order by t2.id) as seqnum
      from table2 t2
     ) t2
     on t1.primary = t2.id full outer join
     (select t2.*, row_number() over (partition by t2.id order by t2.id) as seqnum
      from table2 t2
     ) t2a
      on t1.secondary = t2a.id and t2.seqnum = t2a.seqnum;
Comments