CODEWITHSUNDEEP
db2
callee.args
callee.args

Reputation: 439

DB2 Lookup table using two columns of same table

I have a lookup table for institution id, name, address and another table for course details.

In each course record there will be two columns pointing primary and secondary institution ids.

My select query should look like ->

Select course_id,
       name,
       primary_Institution_id,
       Primary_Institution_name,
       primary_Institution_address,
       Secondary _Institution_id,
       Secondary _Institution_name,
       Secondary_Institution_address
  from [JOIN MAY BE] 
  where course_id in ('1223','34234','43432')

How to achieve this? I have no control over the tables and I can only select from them and cannot modify their structure.

Upvotes: 0

Views: 954

Answers (1)

WarrenT
WarrenT

Reputation: 4542

If you are trying to ask how to do the join, it might look something like this

Select c.course_id,
       c.name,
       c.primary_Institution_id,
       i.name       as primary_Institution_name,
       i.address    as primary_Institution_address
       c.secondary_Institution_id
       k.name       as Secondary _Institution_name,
       k.address    as Secondary_Institution_address
  from courses as c
  join institutions  as i 
                     on i.id = c.primary_Institution_id
  left 
  join institutions  as k 
                     on i.id = c.secondary_Institution_id 
  where course_id in ('1223','34234','43432')

This assumes that the first institution id is mandatory (never null) so the join is implied as an inner join, but that perhaps the second might be optional (null allowed) so it uses a left join, in case there is nothing to match to.

Upvotes: 1

Related Questions