Posted by Erland Sommarskog on 05/25/05 23:15

(b_naick@yahoo.ca) writes:
> I need help building the following query..
>
> My table has the following schema: eventID, typeID
>
> Sample Rows:
>
> 1,1
> 1,2
> 1,3
> 2,1
> 3,2
> 3,2
> 4,3
> 4,4
> 5,2
>
> I want to be able to query for all eventID's such that type = 2 and
> type <> 1. So the result should be
>
> 3,2
> 4,2
>
> The result should NOT include 1,2 because eventID 1 is also "related"
> to typeID 1 and 3.

I assume that desired result is

3,2
5,2

Else there is something I don't understand at all.

This could be a good query:

SELECT *
FROM tbl a
WHERE a.type = 2
AND NOT EXISTS (SELECT *
FROM tbl b
WHERE a.eventID = b.eventID
AND EXISTS (SELECT *
FROM tbl c
WHERE c.eventID = b.eventID
ABD c.type = 1))

Since you did not include CREATE TABLE and INSERT statements, I
have not tested this.





--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]