SELECT statement

I have tw开发者_JS百科o 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

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.

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

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.

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

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