Re: SQL Puzzle

From: Lisa Hudd <lisa_at_e-dba.net>
Date: Mon, 16 Aug 1999 14:19:48 GMT
Message-ID: <37bc1dd9.26278940@nntp.netcomuk.co.uk>

On Mon, 16 Aug 1999 13:05:32 GMT, "Eric" <ebektech_at_videotron.ca> wrote:

>I am trying to find diff alternatives to resolve the following:
>
>I have the following 2 tables:
>
>CLIENT
>client_id
>...
>
>PROGRAM
>client_id
>program_id
>effective_date
>...
>
>A client can have multiple programs... Now, I need to write a query for a
>client to find:
>
>If the client has no programs or all the programs are not equal 8 then, the
>query should return 'Not 8', client_id.....
>
>If the client has all programs equal to 8 then, the query should return
>'All 8', client_id.....
>
>Only one row should be returned for each client.
>
>Thanks

I am sure there are more elegant ways to write this, but below is one quick way to accomplish this. You did not specify whether the effective date will play a part in this or what is the actual value we are expecting in the program_id column (is it a numeric or char and do we add all program_id's together to get '8' or each program_id should contain the value 8?), but this is one approximate SQL statement :

select c.client_id, 'All 8'
  from client c
 where (not exists ( select 'x' from program p

                             where   p.client_id = c.client_id
                                  and  p.program_id != 8)
             and exists (select 'x' from program p2
                               where p2.client_id = c.client_id) )

union
select c.client_id , 'Not 8'
  from client c
where ( (not exists (select 'x' from program p

                                 where    p.client_id  = c.client_id)
                or  exists (select 'x' from program p2
                                 where   p2.client_id = c.client_id
                                    and   p2.program_id != 8) )

• Lisa

Received on Mon Aug 16 1999 - 09:19:48 CDT