Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: oracle SQL question..
See below
In article <cellis-ya02408000R2805981313460001_at_news.clubi.ie>,
cellis_at_iol.ie (Chrysalis) wrote:
>In article <6ki3m2$7sd$2_at_engnews2.Eng.Sun.COM>, gosar_at_EBB.Eng.Sun.COM wrote:
>
>> Hi,
>>
>> I have table ....
>>
>> KC MKT PLT QTY
>> ---------- ---------- --- ----------
>> 100 M1 P1 1
>> 100 M1 P1 2
>> 100 M1 P1 3
>> 200 M2 P1 1
>> 300 M3 P3 1
>> 200 M2 P2 1
>> 100 M1 P2 1
>>
>> I want to select everything which is in PLT='P1' and
>> only those records from other PLT's which have different
>> combination of "KC, MKT and QTY fields"....
>>
>> snip
>
>select ...
>from your_table
>where plt = 'P1'
>UNION
>select ...
>from your_table T1
>where plt <> 'P1'
>and not exists
>(select null from your_table T2
>where T2.plt = 'P1'
> and (T2.kc,T2.mkt,T2.qty) in ((T1.kc,T1.mkt,T1.qty)));
>
I may have missed something in the question, but wouldn't the DISTINCT parameter do what was asked. I.E. SELECT DISTINCT kc, mkt, plt, qty WHERE plt = 'P1'
HTH
James
-- lorenzen_at_tiny.net | Life is complex; it has | real and imaginary partsReceived on Thu May 28 1998 - 00:00:00 CDT