Join tables to retrieve data in DB2

Question

These are my table details:

Table1

id       name
----------------
101      syed
102      shaik

Table2

l_id     sup_id
-----------------
101     102


id  name    sup_id  sup_name
------------------------------
101 syed    102     shaik

In table-1 I have two rows, 101 and 102 which is a master table. 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 
    r.id, 
    trim(r.name) as name, trim(a.sup_id) as sup_id,
    trim(select name from schema.table1 
         where id in (a.sup_id)) as sup_name 
FROM 
    schema.table1 r
JOIN
    schema.table2 a ON a.l_id = r.id 
WHERE 
    r.id IN (101)

I'm able to get till supervisor id, but not supervisor name.

Any ideas would be greatly appreciated

person id - person name - supervisor id - supervisor name

Please, show your current script and explain, why you cannot join table1 and table2 2 times by different id from table2? — astentx
– astentx, Commented Nov 12, 2020 at 5:57
You can join any (of course, it is limited, but you hardly can hit that number) number of tables and use a single table any number of times in join, but should use alias for each occurrence (JOIN tabname as tabalias). No need to do subquery in select, it often performs bad. — astentx
– astentx, Commented Nov 12, 2020 at 6:16

Fahmi · Accepted Answer · 2020-11-12 06:04:45Z

1

You can try the below -

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

answered Nov 12, 2020 at 6:04

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

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

4 Comments

Syed Over a year ago

What is the alias you have to connect to sup_id?

Fahmi Over a year ago

it's the t2.id which is a reference of table1 @Syed - you need to join your table2 with the multiple instances of table1

Syed Over a year ago

join table1 t2 on t2.id=t.sup_id --> In this statement, there is no id column in t2 table. There is only l_id and sup_id columns

Fahmi Over a year ago

@Syed, t2 is an alias of table1 and table1 has an id column which is used for joining with sup_id

Collectives™ on Stack Overflow

Join tables to retrieve data in DB2

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related