Complex SQL Qry?

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 Thu May 11 2000 - 00:00:00 CDT