Re: Complex SQL Qry?

From: Kosta <kosta_mastakov_at_insumak.com.mk>
Date: 2000/05/17
Message-ID: <39229f80@news.mt.net.mk>#1/1

Hi,

Try thie query

 select taba.code "CodeFromA",nvl(tabb.code,'n/a') "MatchToB"  from (select comp_name,prod,nvl(descr,'X') descr,code

       from companytab
       where comp_name='CompanyA') taba,
      (select comp_name,prod,nvl(descr,'X') descr,code
       from companytab
       where comp_name='CompanyB') tabb

 where taba.prod=tabb.prod (+) and taba.descr=tabb.descr (+)

Regards,
 Kosta

prince_kumar_at_my-deja.com wrote in message <8ffess$6kp$1_at_nnrp1.deja.com>...
>I have a table CompanyTab like the following one:
>
>Comp_name Prod Desc Int_code
>--------- ----- ----- --------
>CompanyA prod1 desc1 CAP1D1 -- 1
>CompanyA prod1 NULL CAP1Z1 -- 2 block A
>CompanyA prod1 desc2 CAP1D2 -- 3
>CompanyA prod2 desc1 CAP2D1 -- 4
>
>CompanyB prod1 desc2 CBP1D2 -- 5
>CompanyB prod1 desc3 CBP1D3 -- 6 block B
>CompanyB prod1 NULL CBP1ZZ -- 7
>
>How do I list all the Internal codes (Int_code which is the combination
>of Prod and Desc field for each comp_name) corresponding to CompanyB,
>if there is a matching Prod, Desc in the CompanyA?
>If there is no matching, I need to diplay 'n/a' .
>
>The result should be
>
>n/a - record 1 of companyA matches with nothing on companyB
>CBP1ZZ - record 2 of companyA matches with 7 of companyB
>CBP1D2 - record 3 of companyA matches with 5 of companyB
>n/a - record 4 of companyA matches with nothing on companyB
>
>Your help is very much appreciated?
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Wed May 17 2000 - 00:00:00 CDT