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 
Rent Charter Buses Company
READ ALSO
Using Curl in codeigniter like this

Using Curl in codeigniter like this

How to use cURL in codeigniter? I have API access like this, and i want use in codeigniter

300
Swift3 api communication with mysql

Swift3 api communication with mysql

In my login API I can not get the value of my emailIf I am in php / mysql how to recover a variable

310