SQL query problem when a column is null. Returns empty or cartesian

[Bullits]

Hi there
I am new to SQL. I am making a simple employee phonebook with tables employee, job, and department. (Employee is recursive in that it also stores the managers info.)

My statement works as long as mgr_id is not null (employee table). When it is null an empty result set is returned - but I know there is an employee beginning with C in that table.

My statement:
SELECT e.employee_id, e.surname, e.firstname, e.phonenum, d.dept_id, d.deptname, j.job_id, j.jobname, e.mgr_id, e1.surname AS mgrsurname, e1.firstname AS mgrfirstname
FROM employee AS e, employee AS e1, department AS d, job AS j
WHERE e.surname LIKE 'C%'
AND e.dept_id = d.dept_id
AND e.job_id = j.job_id
AND e.mgr_id = e1.employee_id;

//problem with query - no results when mgr is null.

//example results (WHERE e.surname LIKE 'p%')
Emp Id Surname First name Phone Dept Job Mgr
6 Patrick Tara 5551232 Graphics Senior Graphics Artist John Keogh

Can someone help me?
When I set the sql statement to allow a null for mgr_id I am getting a cartesian result instead of one line. I.e I get 7 results with the same name (as there are 7 entries in the dbase)

 

[Golom]

SELECT e.employee_id, e.surname, e.firstname, e.phonenum,
       d.dept_id, d.deptname, j.job_id, j.jobname,
       NZ(e.mgr_id,""), 
       NZ(e1.surname,"<NO MANAGER>") AS mgrsurname,
       NZ(e1.firstname,"") AS mgrfirstname

FROM ((employee AS e INNER JOIN department AS d
       ON e.Dept_id = d.dept_id)
       INNER JOIN job AS j
       ON e.job_id = j.job_id)
       LEFT JOIN employee AS e1
       ON e.mgr_id = e1.employee_id  

WHERE e.surname LIKE 'C%' ;

 

[Golom]

Sorry. Forgot which forum I was in

SELECT e.employee_id, e.surname, e.firstname, e.phonenum,
       d.dept_id, d.deptname, j.job_id, j.jobname,
       IsNull(e.mgr_id,""), 
       IsNull(e1.surname,"<NO MANAGER>") AS mgrsurname,
       IsNull(e1.firstname,"") AS mgrfirstname

FROM ((employee AS e INNER JOIN department AS d
       ON e.Dept_id = d.dept_id)
       INNER JOIN job AS j
       ON e.job_id = j.job_id)
       LEFT JOIN employee AS e1
       ON e.mgr_id = e1.employee_id  

WHERE e.surname LIKE 'C%' ;

 

[PHV]

And what about this ?
SELECT e.employee_id, e.surname, e.firstname, e.phonenum, d.dept_id, d.deptname, j.job_id, j.jobname, e.mgr_id, e1.surname AS mgrsurname, e1.firstname AS mgrfirstname
FROM employee AS e
INNER JOIN department AS d ON e.dept_id = d.dept_id
INNER JOIN job AS j ON e.job_id = j.job_id
LEFT JOIN employee AS e1 ON e.mgr_id = e1.employee_id
WHERE e.surname LIKE 'C%';

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Bullits]

Thanks for the help everyone. I should have mentioned it's mysql I am using.

PHV's answer works straight away.

IsNull(e.mgr_id,""), gives an error on mysql - is there an equivalent to use?

Thanks again

 

[r937]

use COALESCE(e.mgr_id,'')

standard sql ;-)

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)