Re: need help to create query

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 06/20/04 

Date: Mon, 21 Jun 2004 00:14:16 +0200

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)

Relevant Pages

  • Re: query
    ... >I have a column in an SQL table. ... Hi Freger, ... Best, Hugo ... (Remove _NO_ and _SPAM_ to get my e-mail address) ...
    (microsoft.public.sqlserver.mseq)
  • Re: TSQL equivalent of VBA
    ... >in SQL? ... I presume that I need a CASE statement which I can write, ... Best, Hugo ... (Remove _NO_ and _SPAM_ to get my e-mail address) ...
    (microsoft.public.sqlserver.programming)
  • Re: newest date
    ... >data set, in order to give it out in a new column 'newest date'. ... And I can't test my SQL, ... Best, Hugo ... (Remove _NO_ and _SPAM_ to get my e-mail address) ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored Procedure
    ... Hi Phoon, ... from VB to SQL Server. ... Best, Hugo ... (Remove _NO_ and _SPAM_ to get my e-mail address) ...
    (microsoft.public.sqlserver.server)
  • Re: Object-oriented SQL statements
    ... Successful consultants usually see the financial advantage in that. ... Yet they say they have a happy marriage which confines them to the SQL ... verifiable conceptual model. ... I just wrote off the original post as spam as soon as I saw it. ...
    (comp.databases.theory)