Extracting data from a table when matching entry in one table but not another

[Buchead]

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.

[mwasif]

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

[Buchead]

Fantastic. Thanks for that.

[mwasif]

You are welcome.