UNION STATEMENT

[Angelo luis]

I WANT TO DO A QUERY LIKE THIS, BUT THE MYSQL GIVE A ERROR.

TO UNDERSTAND MY PROBLEM SUPPOSE THIS 2 TABLES:

TABLE 1 = EMPLOYEE

ID | NAME | DEPARTAMENT_ID
1 | EDDIE | 1
2 | JONH | 1
3 | MARIA | 2
4 | ROGER | null


TABLE 2 = DEPARTAMENT


ID | NAME
1 | SALES
2 | FINANCIAL
3 | HUMAN RESOURCES


I WANT THIS RESULT SET:

'DEPARTAMENT NAME' | 'COUNT(*)'
FINANCIAL | 1
HUMAN RESOURCES | 0
SALES | 2 <- HERE I ORDER BY DEPARTAMENT NAME, BUT...
NO DEPARTAMENT | 0 <- EMPLOYYE WITHOUT DEPARTAMENT IS TH LAST
POSITION OF THE RESULT SET



LOOK MY QUERY:

((SELECT D.NAME AS 'DEPARTAMENT NAME', COUNT (*)
FROM DEPARTAMENT D JOIN EMPLOYYE E ON E.DEPARTAMENT_ID = D.ID
GROUP BY D.NAME) # A simple group by

UNION

(SELECT D.NAME , 0
FROM DEPARTAMENT D
WHERE D.ID NOT IN ( SELECT D1.ID
FROM DEPARTAMENT D1
JOIN EMPLOYYE E ON E.DEPARTAMENT_ID = D1.ID
)) # here i list the departaments with no employee
ORDER BY 'DEPARTAMENT NAME' )
# here is the problem, without this(ORDER BY CLAUSE) the query works, but with this i have a query sintax error in the
# parenteses... Remember, I want to order just the 2 first union, the count of employee without
# departament must be in the last position of the result set

UNION

SELECT IF(E.DEPARTAMENT_ID IS NULL,'NO DEPARTAMENT', E.DEPARTAMENT_ID), COUNT(*)
FROM EMPLOYEE E
WHERE E.DEPARTAMENT_ID IS NULL
GROUP BY E.DEPARTAMENT_ID # WHERE I COUNT THE EMPLOYEE WITHOUT DEPARTAMENT



Anyone have a solution for my problem??? I real problem dont envolve this tables, but they illustrate my problem!!!



Edited 2 time(s). Last edit at 11/18/2005 06:07AM by Angelo luis.