Path: utzoo!attcan!uunet!fernwood!oracle!news
From: dkennedy@oracle.uucp (DAN KENNEDY)
Newsgroups: comp.databases
Subject: Re: SQL Question
Keywords: SQL
Message-ID: <1990Oct10.121246.8576@oracle.com>
Date: 10 Oct 90 12:12:46 GMT
References: <391@ulticorp.UUCP> <1090@lsicom2.UU.NET>
Reply-To: dkennedy@oracle.UUCP (DAN KENNEDY)
Organization: Oracle Corporation, Boston, MA
Lines: 38

In article <1090@lsicom2.UU.NET> root@lsicom2.UU.NET (Admin) writes:
>
>>Given a table of patients PID int   WEIGHT int
>>where one PID may have numerous weight readings.
>
>>How would I select the PID for only those patients whose
>>weight was ALWAYS <= 100.
>
>
>This isn't specifically Gupta........
>
>
>select PID from table
>where not exists
>	(select PID from table
>	where WEIGHT > 100)

... and it won't work 

The query above will return all the rows from "table" if anyone
weighs > 100.


Try...

select distinct a.pid from table a
 where not exists 
        (select 'foo' from table b
          where a.pid = b.pid
            and b.weight > 100)


The "a" and "b" are table aliases used to avoid confusion in the subquery.
The distinct simply assures that your output doesn't list the same PID
more than once. 


-- Dan