Using NOT EXISTS on composite Key
-
Hi,
I'm having trouble using NOT EXISTS. I have a nested query that returns all records that I don't want. I'm then using an outer query to return all the records that don't exist within the sub query.
Does anyone know any good resources that discuss using a difference query on tables with composite keys? In the below example both HostName and ApplicationName form the primary key along with another field UserID. Based on my test data this isn't returning what I expect.
SELECT HostName, ApplicationName FROM AppUsers WHERE NOT EXISTS
(SELECT HostName, ApplicationName
FROM AppUsers
WHERE IsCustodian = 1
GROUP BY HostName, ApplicationName)
GROUP BY HostName, ApplicationName
Thanks, Jon
-
How bout something like?
SELECT A1.HostName, A1.ApplicationName
FROM AppUsers A1
LEFT JOIN (SELECT HostName, ApplicationName
FROM AppUsers
WHERE IsCustodian = 1)
A2 ON A1.HostName = A2.HostName
AND A1.ApplicationName = A2.ApplicationName
WHERE A2.HostName IS NULL AND A2.ApplicationName IS NULL
GROUP BY A1.HostName, A1.ApplicationName
Good Hunting!AJ Ahrens
webmaster@kritter.net -
or
SELECT HostName, ApplicationName
FROM AppUsers
GROUP BY HostName, ApplicationName
HAVING SUM(CASE WHEN IsCustodian=1 THEN 1 ELSE 0 END) = 0
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks !!!
-
Just to add my twopenneth worth - although it's more about performance than actually solving your problem.
You don't need to specify field names in the existence select or, in this scenario, the grouping, so;
SELECT HostName, ApplicationName FROM AppUsers WHERE NOT EXISTS
(SELECT HostName, ApplicationName
FROM AppUsers
WHERE IsCustodian = 1
GROUP BY HostName, ApplicationName)
GROUP BY HostName, ApplicationName
should be
SELECT HostName, ApplicationName FROM AppUsers WHERE NOT EXISTS
(SELECT * FROM AppUsers WHERE IsCustodian = 1 )
GROUP BY HostName, ApplicationName
Select HostName, ApplicationName From AppUsers
takes more processing than
Select * From AppUsers
remember in an existence check, you only want to know if records exists (or NOT) with a certain criteria, you don't care what fields are returned.
Again the Group By clause within the existence select serves you no purpose, it will take longer to process the Group By than to let it just a return a simple result set (albeit bigger, but you won't see it anyway).
Regards
Chris
-
very clever ... Approaching the problem from a different point of view, you are! MAX() you may use, instead of SUM, should you desire.
Beware of EXISTS() .... unless contained query is explicitly related to outer query (i.e., "joined" using a WHERE clause), no relation do they have! EXISTS() is not a relation or a join, rather a boolean expression, simply returning True or False.
-
David,
as usual you show me where I still have MUCH to learn...
Good Hunting!AJ Ahrens
webmaster@kritter.net -
The problem you have here is that the inner query is not linked back to the outer query.
When you are saying "WHERE NOT EXISTS (...)" all that matters is that it returns true or false for a result to be returned (as mentioned ealier by Yoda).
So the following query should work in the style you are using
SELECT HostName, ApplicationName
FROM AppUsers
WHERE NOT EXISTS
(
SELECT *
FROM AppUsers ausr
WHERE ausr.IsCustodian = 1
AND ausr.HostName = AppUsers.HostName
AND ausr.ApplicationName = AppUsers.ApplicationName
)
GROUP BY HostName, ApplicationName
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply