Arun Kumar Arun Kumar - 3 months ago 8
MySQL Question

SQL Query Is Giving Wrong Output

i have three tables EMPLOYEE,EMPLOYEE_UNIT,UNIT
i want employee details including name and employee unit names for single user based on employee_id(For example employee 1 is working for 2 different units..
i want employee name with working units:

TABLE STRUCTURE AS FOLLOWS:
EMPLOYEE TABLE
EMPLOYEE_ID,FIRST_NAME,LAST_NAME,PHONE_NUMBER
1 emp1 emp1 ***********
2 emp2 emp2 ***********
3 emp3 emp3 ***********
4 emp4 emp4 ***********
UNIT TABLE
UNIT_ID,UNIT_TITLE,CITY
1 unit1 city1
2 unit2 city2
3 unit3 city3

EMPLOYEE_UNIT TABLE
EMP_UNIT_ID,EMPLOYEE_ID,UNIT_ID
1 1 2
2 1 3
3 2 1


MyQUERY:

SELECT DISTINCT CONCAT(FIRST_NAME,LAST_NAME) AS TITLE,PHONE_NUMBER
FROM EMPLOYEE AS E
LEFT JOIN EMPLOYEE_UNIT AS EU ON EU.EMPLOYEE_ID = E.EMPLOYEE_ID
WHERE E.EMPLOYEE_ID = 137

woz woz
Answer

For a list of employee names and corresponding unit titles, use this:

SELECT e.FIRST_NAME, e.LAST_NAME, GROUP_CONCAT(DISTINCT u.UNIT_TITLE SEPARATOR ', ')
FROM EMPLOYEE e
JOIN EMPLOYEE_UNIT m ON e.EMPLOYEE_ID = m.EMP_ID
JOIN UNIT u ON m.UNIT_ID = u.UNIT_ID
GROUP BY e.EMPLOYEE_ID

MySQL has GROUP_CONCAT for this type of thing.

Comments