Simple join 1

[Naoise]

I have 3 tables

tblGarages
---------------------
GarageID | CompanyName

tblGarageMechanics
----------------------
GarageID | CertMechID

tblCertifiedMechanics
--------------------------
CertMechID | Mechanic Name

A garage can have mechanics who are certified
and not. I want to display all Garages that HAVE NO certified mechanics.

SELECT DISTINCT(a.GarageID), CompanyName
FROM tblGarages a, tblGarageMechanics b, tblCertifiedMechanics c
WHERE a.GarageID = b.GarageID
AND b.CertMechID <> c.CertMechID
ORDER BY CompanyName

This obviously doesn't work as it will display a garage name if some of the garage mechanics are uncertified.

Any ideas?

Thanks for replies

 

[PHV]

Garages that have mechanics not certified:
SELECT a.GarageID, CompanyName, b.CertMechID AS UncertifiedMechID
FROM tblGarages a INNER JOIN (
tblGarageMechanics b LEFT JOIN tblCertifiedMechanics c ON b.CertMechID = c.CertMechID
) ON a.GarageID = b.GarageID
WHERE c.CertMechID Is Null

Garages that have no one certified mechanics:
SELECT a.GarageID, CompanyName
FROM tblGarages a INNER JOIN (
tblGarageMechanics b LEFT JOIN tblCertifiedMechanics c ON b.CertMechID = c.CertMechID
) ON a.GarageID = b.GarageID
GROUP BY a.GarageID, CompanyName
HAVING Count(c.CertMechID) = 0

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

 

[Naoise]

Thanks very much for the reply, it works well but there are garages that have not been assigned any mechanics yet and I would like those to appear as well as being a garage where there are no certified mechanics. Could I trouble you to tweak your supplied SQL?

Thanks again,
Naoise

 

[PHV]

You may try this:
SELECT a.GarageID, CompanyName
FROM tblGarages a LEFT JOIN (
tblGarageMechanics b INNER JOIN tblCertifiedMechanics c ON b.CertMechID = c.CertMechID
) ON a.GarageID = b.GarageID
GROUP BY a.GarageID, CompanyName
HAVING Count(b.CertMechID) = 0

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