Posted by MGFoster on 05/25/05 23:02

b_naick@yahoo.ca wrote:
> 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.
>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Shouldn't the last pair be 5,2, since there isn't any 4,2.

Try,

SELECT DISTINCT eventID, typeID
FROM t as t1
WHERE typeID = 2
AND eventID NOT IN (SELECT eventID FROM t WHERE typeID != 2)

Change table name "t" to your table's true name.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQpTZ5IechKqOuFEgEQKIrgCfeW81ytgRIXUnl//jAA0RU8zZwLQAoOPN
UBuYtqvs/JqhLjVuFIYYTzqF
=aqWw
-----END PGP SIGNATURE-----

• Next in forum: Re: Easy way to remove international alphabet from all rows?
• Prev in forum: Re: BCP fmt and Create Table
• Thread view: Re: Help with SQL Query

[Reply to this message]