Asim Ahmed Asim Ahmed - 21 days ago 8
SQL Question

creating new columns based off where conditions of existing table

i have a table that has everyone's email address only once next to their Member ID but i want to create columns that has their leadership email addresses in the same row.

+-----------+--------+----------+----------+---------+
| member_id | name | Email | Dir_ID | Leader_ID|
+-----------+--------+----------+----------+----------
| 105 | Andri | em100 | 106 | 110 |
| 106 | Steve | em10 | Null | Null
| 110 | Soraya | em101 | 110 | 110
| 111 | Eva | em20 | 110 | 105
| 112 | Sonia | em40 | 105 | 111


i'm trying to accomplish the following

+-----------+--------+----------+----------+-------------+
| member_id | name | Email | Dir_Email| Leader_EMail|
+-----------+--------+----------+----------+-------------+
| 105 | Andri | em100 | em10 | em101 |
| 106 | Steve | em10 | Null | Null
| 110 | Soraya | em101 | em10 | em10
| 111 | Eva | em20 | em101 | em100
| 112 | Sonia | em40 | em100| em20


any info of where to start would be appreciated. i was thinking of creating a dim table and then doing a few joins but figured there has to be an easier way.

Answer

You can use an hierarchical relationship

   select a.member_id, a.name, a.Email, b.Dir_Email, a.Leader_ID
   from myTable as a 
   left join my_table b on a.dir_id = b.member_id