MySQL match value from one column to another and return a value

267

August 12, 2017, at 04:41 AM

Username    Employee_Name   Is_Employed L4_Username
aaa123      A1              Y           aaa131
aaa124      A2              Y           aaa131
aaa125      A3              Y           aaa131
aaa126      A4              Y           aaa131
aaa127      A5              Y           aaa131
aaa128      A6              Y           aaa131
aaa129      A7              Y           aaa131
aaa130      A8              Y           aaa130
aaa131      A9              Y           aaa130

what I need to do with this data is try and make a statement that can find that aaa131 (for example) and return the employee name for that L4_Username and present it AS L4_Employeename so I get the following:

Username    Employee_Name   Is_Employed L4_Username L4_Employeename
aaa123      A1              Y           aaa131      A9
aaa124      A2              Y           aaa131      A9
aaa125      A3              Y           aaa131      A9
aaa126      A4              Y           aaa131      A9
aaa127      A5              Y           aaa131      A9
aaa128      A6              Y           aaa131      A9
aaa129      A7              Y           aaa131      A9
aaa130      A8              Y           aaa130      A8
aaa131      A9              Y           aaa130      A8

It basically needs to do like an index match and return employee_name AS L4_EmployeeName

Any ideas?

Here is where i am starting

SELECT *
FROM Employee_Team a
    INNER JOIN Workforce_Detail b ON a.Username = b.Username
WHERE Is_Employed = 'Y'

Edit: Per comments I wasn't clear enough about what tables had what:

Username is in both tables.
L4_Username is only in Workforce_Detail
Employee_Name is only in Workforce_Detail

Answer 1

you could use a self join

you could use a double join on Workforce_Detail

  SELECT  
     a.Username
   , c.Employee_Name
   , a.Is_Employed
   , c.L4_Username
   , b.Employee_Name as L4_Employeename
  FROM Employee_Team a
  INNER JOIN Workforce_Detail c on a.Username = c.Username a.Is_Employed = 'Y'
  INNER JOIN Workforce_Detail b ON b.L4_Username = a.Username