Monday, March 19, 2012

exclude condition

i have two tables: "Person" and "Year". "Person" can have many "Year"(one to many relation). i want a query which returns all the recordsfrom "Person" where "Year" is 2005 but exclude if there is any "Year"with 2004. how can i write that query? any help will be appreciated.
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