Re: SQL Puzzle

Hi Eric,

Try this:

select

    t1.client_id,
    decode(sum(decode(nvl(t2.program_id,0),8,0,1)),0,'All 8','Not 8') from

    client t1,
    program t2,
where

    t1.client_id = t2.client_id(+)
group by

    t1.client_id;

Hth

Jin Bo

Eric 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
Received on Tue Aug 17 1999 - 03:16:14 CDT