List:General Discussion« Previous MessageNext Message »
From:BobSharp Date:March 17 2009 11:09pm
Subject:Re: Negated SELECT query
View as plain text  
Thanks for that,    worked through and found that this gives the correct 
result ...

---  Employee No Assets  --- 
SELECT  DISTINCT e.employeeID AS eID,
                concat(e.firstname, " ", e.lastname) AS eName
FROM employees e LEFT JOIN assets a ON e.employeeID = a.employeeID
WHERE  e.employeeID  IS NULL
ORDER BY   e.employeeID




---  Employee No History  --- 
SELECT   DISTINCT a.assetID  AS aCode,
                LEFT(a.assetdescription,60) AS aTitle,
                c.assetcategory AS cCategory
FROM assets a LEFT JOIN maintenance m ON m.assetID = a.assetID
LEFT JOIN assetcategories c ON a.assetcategoryID = c.assetcategoryID
WHERE m.assetID  IS NULL
ORDER BY  a.assetID



Cheers




----- Original Message ----- 
From: "Bonnett, John" <John.Bonnett@stripped>
To: <bobsharp@stripped>; <mysql@stripped>
Cc: <win32@stripped>; <mysql-help@stripped>
Sent: Tuesday, March 17, 2009 10:59 PM
Subject: RE: Negated SELECT query


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:bobsharp@stripped]
Sent: Wednesday, 18 March 2009 3:13 AM
To: mysql@stripped
Cc: win32@stripped; mysql-help@stripped
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=1



--------------------------------------------------------------------------------



No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.0.237 / Virus Database: 270.11.18/2008 - Release Date: 03/17/09 
16:25:00


-- 
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


Thread
Negated SELECT queryBobSharp17 Mar
  • Re: Negated SELECT queryPerrin Harkins17 Mar
  • RE: Negated SELECT queryJohn Bonnett17 Mar
  • Re: Negated SELECT queryBobSharp18 Mar