November 17, 2006 at 5:18 am
An odd problem:
In my tblLinks I have pointers (column ProjectID) to the ID field of tblProjects.
I want to know if I have rows in tblProjects which have no corresponding entry in tblLinks. (The column ProjectID in tblLinks is on the "many" side of the "one-to-many" relationship with the "one" ID in tblProjects)
This query below works well and shows 8 rows with no entry in tblLinks (correctly).
SELECT ID FROM tblProjects WHERE
NOT EXISTS (SELECT ProjectID FROM tblLinks
WHERE tblProjects.ID = ProjectID)
... what I would like to know is why my first attempt below returned no rows?
SELECT ID FROM tblProjects WHERE
NOT (ID IN (SELECT ProjectID FROM tblLinks))
I even tried playing with the syntax, as below, but still got no rows!
SELECT ID FROM tblProjects WHERE
ID NOT IN (SELECT ProjectID FROM tblLinks)
Strange.
November 17, 2006 at 5:48 am
hai,
i tried these queries in sql 2000. All queries returns the same answer.
Verify ur tables in column ID has null values.
To my knowledge first and third are correct queries...
but second is not correct one... but it is working well...
November 17, 2006 at 11:27 am
If tblLinks also has an ID column, then the optimizer may be getting confused about which ID column to use.
With more than one table, it is good practise to use aliases and reference each column with its table alias.
The following should work:
SELECT P.[ID]
FROM tblProjects P
WHERE P.[ID] NOT IN (
SELECT L.ProjectID
FROM tblLinks L)
My experience is that an EXISTS subquery can by more efficient so you should probably use something like:
SELECT P.[ID]
FROM tblProjects P
WHERE NOT EXISTS (
SELECT 'AnyThingYouWantAsItIsNotUsed'
FROM tblLinks L
WHERE L.ProjectID = P.[ID])
November 17, 2006 at 11:36 am
SELECT ID FROM tblProjects WHERE
NOT (ID IN (SELECT ProjectID FROM tblLinks))
the above would return no rows if there is a ProjectId that is NULL.
change to this:
SELECT ID FROM tblProjects WHERE
NOT (ID IN (SELECT ProjectID FROM tblLinks AND ProjectID Is Not NULL))
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply