SELECT Employees.* FROM Employees LEFT JOIN Assets ON Employess.EmployeeID = Assets.EmployeeID WHERE Assets.EmployeeID IS NULL
The one for assets with no maintenance is similar. The point is the left join above produces in its output all rows from the Employees table regardless of whether anything matches in the assets table. By selecting only rows where the foreign key field in the assets table is null gives you the employees having no assets. John Bonnett -----Original Message----- From: BobSharp [mailto:bobsh...@ntlworld.com] Sent: Wednesday, 18 March 2009 3:13 AM To: mysql@lists.mysql.com Cc: wi...@lists.mysql.com; mysql-h...@lists.mysql.com Subject: Negated SELECT query 3 tables are related by one-many links. Employees ---<- Assets ---<- Maintenance Employees can be assigned => 0 Assets Assets can have => 0 occurances of Maintenance. Assets table contains EmployeeIDs and MaintenanceIDs, but no Foreign Key contraints. Queries ... 1) which Employees do not have any Assets ? 2) which Assets have not had any Maintenance ? These have been written successfully with Sub-Queries, I would like to know how they can be done with only JOINs ? (that also means without the EXCEPT statement) Is that too much of a challenge ? (MySQL 5.0.67) -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 12747 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
