i did try
<code>
SELECT * FROM Person JOIN Year ON Person.Id = Year.PersonID WHERE Year.Year = 2005 AND Year.Year <> 2004
</code>
but it doesn't seem to work. i want this query to return records fromPerson where there is no any year with 2004 but only 2005. If a personhas both 2004 and 2005 exclude that person.
My first thought is this:
SELECT
*
FROM
Person
LEFT OUTER JOIN
Year AS Year2004 ON Person.Id = Year.PersonID AND Year2004 = 2004
LEFT OUTER JOIN
Year AS Year2005 ON Person.Id = Year.PersonID AND Year2005 = 2005
WHERE
Year2004.PersonID IS NULL AND
Year2005.PersonID IS NOT NULL
It could also be accomplished like this:
SELECT
*
FROM
Person
WHERE
NOT EXISTS(SELECT PersonID FROM Year WHERE Person.Id = Year.PersonID AND Year = 2004) AND
EXISTS(SELECT PersonID FROM Year WHERE Person.Id = Year.PersonID AND Year = 2005)
Of the 2, I think the second option will perform better. You should run both in Query Analyzer and decide for yourself.
No comments:
Post a Comment