|
|
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
Navigation:
[Reply to this message]
|