Adnan Adnan - 3 years ago 64
SQL Question

SELECT statement

I have two tables:

EMP

emp_id | Name | Surname |
1 | Bob | Park |
2 | Annie| South |
3 | Eric | P. |


PROJECT

proj_id | Tester_1 | Tester_2 | Tester_3 |
1 | 2 | 3 | 1 |


Now I am trying to make a view, so that I have the following

proj_id | Tester_1_Name | Tester_2_Name | Tester_3_Name
1 | Annie | Eric | Bob

Answer Source

You just have to Join

   Select proj_id, 
      t1.name + ' ' + t1.surname tester1,
      t2.name + ' ' + t2.surname tester2,
      t3.name + ' ' + t3.surname tester3
   From Project p 
     Left Join Emp t1 On t1.emp_id = p.Tester_1
     Left Join Emp t2 On t2.emp_id = p.Tester_2
     Left Join Emp t2 On t3.emp_id = p.Tester_3

As a View...

  Create View ProjectWithTesters
  As 
       Select proj_id, 
          t1.name + ' ' + t1.surname tester1,
          t2.name + ' ' + t2.surname tester2,
          t3.name + ' ' + t3.surname tester3
       From Project p 
         Left Join Emp t1 On t1.emp_id = p.Tester_1
         Left Join Emp t2 On t2.emp_id = p.Tester_2
         Left Join Emp t2 On t3.emp_id = p.Tester_3

NOTE: As others mentioned, your database design violates First Normal Form "There should be no repeating groups", which means that a table should not contain multiple columns which represent multiple instances of some attribute (like your tester_1, tester_2, tester_3). Check out this link: database normalization for more info.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download