Buchead Posted March 30, 2008 Share Posted March 30, 2008 Hello all, I'm experiencing problems extracting some data based on whether an entry is in one table but not another. There are 4 tables: STAFF ----- staffID staffName CALLS ----- callID staffID callTime PROJECT ------- projectID staffID HOLIDAYS -------- holidayID staffID `calls` contains data about the length of phone calls made by each of the members of `staff`. `project` is a list of staff involved in a project, whilst `holidays` is those who aren't in the office. What I want to do is pull all the callTime from `calls` made by staff who are in `holidays` but not in `project`. For some bizarre reason it is possible for staff to be in both `project` and `holidays` so those need to be ignored. I thought about using: SELECT c.callTime from `calls` AS c LEFT JOIN `staff` AS s ON c.staffID=s.staffID LEFT JOIN `project` AS p ON p.staffID=c.staffID LEFT JOIN `holidays` AS h ON h.staffID=c.staffID WHERE XXXX AND p.staffID IS NULL But am not sure what to put in place of XXXX to determine that there is an entry in `holidays`. Thanks for any pointers. Quote Link to comment Share on other sites More sharing options...
mwasif Posted March 30, 2008 Share Posted March 30, 2008 What about using INNER JOIN for all tables except project SELECT c.callTime from `calls` AS c INNER JOIN `staff` AS s ON c.staffID=s.staffID INNER JOIN `holidays` AS h ON h.staffID=c.staffID LEFT JOIN `project` AS p ON p.staffID=c.staffID WHERE p.staffID IS NULL Quote Link to comment Share on other sites More sharing options...
Buchead Posted March 30, 2008 Author Share Posted March 30, 2008 Fantastic. Thanks for that. Quote Link to comment Share on other sites More sharing options...
mwasif Posted March 30, 2008 Share Posted March 30, 2008 You are welcome. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.