CODEWITHSUNDEEP
sqldatabasedb2
Syed
Syed

Reputation: 2607

Query data by joining tables in DB2

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

Upvotes: 1

Views: 686

Answers (2)

user3315556
user3315556

Reputation: 175

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

Upvotes: 1

gotqn
gotqn

Reputation: 43636

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.

Upvotes: 1

Related Questions