"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:lt2cd0tmv4betlfiap37t36ndan05jcd4b@4ax.com...
> On Sun, 20 Jun 2004 18:05:27 -0400, Daniel wrote:
>
> >hi,
> > I had a table like below:
> >
> >id status
> >----------------------------
> >82 N
> >82 N
> >83 N
> >83 Y
> >83 N
> >81 N
> >----------------------------
> >
> >My question is: how to filter out 82,81 because i only want to choose id
> >whose all status is N!
> >
> >It really confused me, and i have no way out!
> >I really appreciate you help, thanks ahead!
> >
>
> Hi Daniel,
>
> You didn't post any DDL, so I'll just have to make a guess:
>
> SELECT DISTINCT id
> FROM YourTable AS a
> WHERE NOT EXISTS
> (SELECT *
> FROM YourTable AS b
> WHERE b.id = a.id
> AND b.status <> 'N')
> (untested)
>
> Translation in plain English: list all id's for which there is no status
> that is not 'N'.
>
> At school I was taught "Don't use no double negations, not never!" This is
> something I had to unlearn when I started doing SQL <g>
>

> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)

On Sun, 20 Jun 2004 18:05:27 -0400, Daniel wrote:

>hi,
> I had a table like below:
>
>id status
>----------------------------
>82 N
>82 N
>83 N
>83 Y
>83 N
>81 N
>----------------------------
>
>My question is: how to filter out 82,81 because i only want to choose id
>whose all status is N!
>
>It really confused me, and i have no way out!
>I really appreciate you help, thanks ahead!
>

"Daniel" <Daniel@yahoo.com> wrote in message news:%23l5g9HxVEHA.3336@TK2MSFTNGP11.phx.gbl...
> hi,
> I had a table like below:
>
> id status
> ----------------------------
> 82 N
> 82 N
> 83 N
> 83 Y
> 83 N
> 81 N
> ----------------------------
>
> My question is: how to filter out 82,81 because i only want to choose id
> whose all status is N!
>
> It really confused me, and i have no way out!
> I really appreciate you help, thanks ahead!

On Sun, 20 Jun 2004 21:55:03 -0400, Steve Kass wrote:

>> At school I was taught "Don't use no double negations, not never!" This is
>> something I had to unlearn when I started doing SQL <g>
>>
>
>You could still please your schoolteacher with
>
>select distinct a.id
>from YourTable as a
>where 'N' = ALL (
> select b.status
> from YourTable as b
> where b.id = a.id
>)
>
>I'm not sure, however, that SQL Server optimizes ALL queries as well as
>EXISTS queries.
>
>Steve Kass
>Drew University