Query data by joining tables in DB2

Question

These are my table details:

Table1

id       name
----------------
101      syed
102      shaik
103      khan

Table2

l_id     sup_id
-----------------
101     102            ----> 102 is supervisor of 101
102     103            ----> 103 is supervisor of 102
103     104            ----> 104 is supervisor of 103

My expected output is to get all data of people along with thier supervisor ids and name.

id  name    sup_id  sup_name
------------------------------
101 syed    102     shaik
102 shaik   103     khan
103 khan                      --> Since 104 is not available in the master table (table 1), it should have as empty values.

In table-1 I have three rows of 101,102,103 which is a master table which will hold all person basic information. Table 2 consists of supervisor login ids with their subordinate ids. The common column between table1 and table2 is id & l_id. So, I'm trying to pull the data as follows to get the details.

My query is as follows

  select t1.id,t1.name, sup_id,t2.name as sup_name
  from table2 t 
 join table1 t1 on t1.id=t.l_id
 join table1 t2 on t2.id=t.sup_id

I'm able to get first 2 rows since they have supervisor id in table 1, but not 3 rd row

Any ideas would be greatly appreciated

person id - person name - supervisor id - supervisor name

gotqn · Accepted Answer · 2020-11-12 19:05:31Z

1

Try this:

 select t1.id,t1.name, sup_id,t2.name as sup_name
 from table2 t 
 left join table1 t1 on t1.id=t.l_id
 left join table1 t2 on t2.id=t.sup_id

The JOIN/INNER JOIN will not return records that have not got corresponding ones in the JOIN table.

You may want to check the different types of joins.

answered Nov 12, 2020 at 19:05

gotqn

44k47 gold badges167 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user3315556 · Accepted Answer · 2020-11-12 20:14:44Z

1

You can also use a subquery if you really want to use a inner join.

select b.id,b.name,b.sup_id,t2.name (select t.id,sup_id,t1.name as name from table2 t inner join table1 t1 on t1.id=t.l_id ) b left join table1 t2 on t2.id=b.sup_id

answered Nov 12, 2020 at 20:14

user3315556

1751 silver badge9 bronze badges

Collectives™ on Stack Overflow

Query data by joining tables in DB2

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related