Re: SQL Puzzle

From: Michel Cadot <micadot_at_altern.org>
Date: Mon, 16 Aug 1999 17:12:17 +0200
Message-ID: <7p99pm$fqc$1@oceanite.cybercable.fr>

Is this your homework?

Nevertheless, here 2 queries to do the job:

select client_id "Id", 'Not 8' "Type" from client a where not exists (select 1 from program b

                  where b.client_id = a.client_id
                    and b.program_id = 8)

union
select client_id "Id", 'All 8' "Type" from program a where not exists (select 1 from program b

                  where b.client_id = a.client_id
                    and b.program_id != 8)

/

Select a.client_id "Id",

       decode(nvl(b.cntt,-1), nvl(b.cnto,-1), 'Not 8', nvl(b.cnt8,-1), 'All 8') "Type"
from client a,

     (select client_id, count(*) cntt,
      sum(decode(program_id, 8, 1, 0)) cnt8,
      sum(decode(program_id, 8, 0, 1)) cnto
      from program group by client_id) b

where b.client_id (+) = a.client_id
  and ( nvl(b.cnto,-1) = nvl(b.cntt,-1)

      or nvl(b.cnt8,-1) = nvl(b.cntt,-1) ) /

Eric a écrit dans le message <01bee7e6$9aaee4c0$9521330a_at_w15-1429.rmoc.on.ca>...
>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
>
Received on Mon Aug 16 1999 - 10:12:17 CDT