How to exclude records in a JOINed table?

[benphp]

I have 4 tables, linked this way:

 

tblCourses.CORID - tblClasses.CORID

tblClasses.CLID - rsTblClass_Students.CLID

rsTblClass_Students.EID - tblEmployee.EID

tblEmployee.EID

 

I'm trying to write a query that will return the names of employees who haven't taken a course (CORID). A record is kept of all the employees (EID) who have taken a class (CLID). I need to find all the employees who have NOT taken any classes.

 

Any help is much appreciated.  I tried this, but it doesn't quite work:

 

Select DISTINCT
tblClasses.CLDate,  
tblClasses.CLID,
rsTblClass_Students.EID,
tblEmployee.ELastName,
tblEmployee.EFirstName,
tblCourses.CORNum,
tblEmployee.EID
FROM tblEmployee 
LEFT OUTER JOIN rsTblClass_Students ON (tblEmployee.EID = rsTblClass_Students.EID)
LEFT OUTER JOIN tblClasses ON (rsTblClass_Students.CLID = tblClasses.CLID)
LEFT OUTER JOIN tblCourses ON (tblClasses.CORID = tblCourses.CORID)
WHERE NOT EXISTS (select DISTINCT EID from rsTblClass_Students, tblCourses where tblEmployee.EID = rsTblClass_Students.EID AND tblCourses.CORID = 589 )
ORDER BY tblEmployee.ELastName

 

[pets2soul]

I think it depends on...what's in the CLID column for the employees not taking any class...

 

Say if CLID column is left empty...then you can put WHERE CLID = ""

 

in another hand, if CLID column is NULL...then... WHERE CLID = NULL

 

That's the simplest way I can think of....hope it helps...

[fenway]

Also, from now on, please continue your previous threads.

[gizmola]

I can't really understand your schema or the joins based on the information you provided, but the way to solve this is this:

 

Do an inner join of employee to classes, in whatever way you need, and only select the employee id.  Add a GROUP BY or DISTINCT so that you only get one row per employee, but this will be the list of employee ids WHO TOOK A CLASS.  Now you convert this into a subselect using NOT IN or a LEFT OUTER JOIN that self joins employee to this result set by ID.  Include an extra column that you can then filter using WHERE IS NULL.

 

You might want to try the subselect first as it's easier to understand that syntax usually.

 

Select employee.... FROM employee WHERE id NOT IN (SELECT ..... inner join of tables to get result set of employees who took a class).

[jcbones]

Are you saying that you wish to find employees, who's id's do NOT exist in CLID table?

 

If there is no matching row for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find rows in a table that have no counterpart in another table:

 

SELECT left_tbl.*

  FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id

  WHERE right_tbl.id IS NULL;

 

This example finds all rows in left_tbl with an id value that is not present in right_tbl (that is, all rows in left_tbl with no corresponding row in right_tbl). This assumes that right_tbl.id is declared NOT NULL.

[kickstart]

Hi

 

For this you need to exclude the other courses before the JOIN then only return rows where the fields from the courses table are null (ie, where they haven't done the specified course).

 

Something like this

 

Select DISTINCT
tblClasses.CLDate,  
tblClasses.CLID,
rsTblClass_Students.EID,
tblEmployee.ELastName,
tblEmployee.EFirstName,
tblCourses.CORNum,
tblEmployee.EID
FROM tblEmployee 
LEFT OUTER JOIN rsTblClass_Students ON (tblEmployee.EID = rsTblClass_Students.EID)
LEFT OUTER JOIN tblClasses ON (rsTblClass_Students.CLID = tblClasses.CLID)
LEFT OUTER JOIN tblCourses ON (tblClasses.CORID = tblCourses.CORID AND tblCourses.CORID = 589)
WHERE tblCourses.CORID IS NULL
ORDER BY tblEmployee.ELastName

 

All the best

 

Keith