SELECT STATMENT

Question

Hello,

I have two table:

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

Any ideas?

Thanx, Adnan

Answer 1

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.

Answer 2

SELECT proj_id, t1.Name, t2.Name, t3.Name FROM PROJECT INNER JOIN EMP as t1 ON PROJECT.Tester_1 = t1.emp_id INNER JOIN EMP as t2 ON PROJECT.Tester_2 = t2.emp_id INNER JOIN EMP as t3 ON PROJECT.Tester_3 = t3.emp_id

Answer 3

You should look into normalizing your database. What happens when you decide that you need 4 testers on a project?

That said, this should give you what you want.

SELECT
     P.proj_id,
     E1.name AS tester_1_name,
     E2.name AS tester_2_name,
     E3.name AS tester_3_name
FROM
     Project P
LEFT OUTER JOIN Emp E1 ON
     E1.emp_id = P.tester_1
LEFT OUTER JOIN Emp E2 ON
     E2.emp_id = P.tester_2
LEFT OUTER JOIN Emp E3 ON
     E3.emp_id = P.tester_3

Answer 4

If you designed these tables then you might want to look up 'normalization' in google, unless a project can only ever have 3 testers...

Answer 5

The solution is DBMS specific. Some DBMSes have utility functions that help with the orthogonalization that you are trying here.

Charles solution is a good start:

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

Other ways for orthogonalization is coalesce and grouping, which is especially useful if there are projects with less testers.