Re: SQL Puzzle

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: 18 Aug 1999 11:38:29 GMT
Message-ID: <7pe5vl$orb$4@news.seed.net.tw>

Eric <ebektech_at_videotron.ca> wrote in message news: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

select c.client_id, decode(count(distinct p.program_id), 8, 'All 8', 'Not 8')   from client c, program p
  where c.client_id=p.client_id(+)
  group by c.client_id; Received on Wed Aug 18 1999 - 06:38:29 CDT