Going mad - outer join problem

From: Jeremy <jeremy0505_at_gmail.com>
Date: Sun, 19 Oct 2008 18:42:21 +0100
Message-ID: <MPG.236581d7b292f2b5989689@News.Individual.NET>

Oracle 10g 10.2.0.1.0

Take two tables A and B.

• ensure you don't have a table A and B before running the following!

drop table a;
drop table b;

create table A

(id 	   number,
 name	   varchar2(10));
 
create table B 
(id 	   number,
 code 	   varchar2(10),

 descrip varchar2(10));

insert into a values (1,'ITEM1');
insert into a values (2,'ITEM2');
insert into a values (3,'ITEM3');

insert into b values (1,'LOC','San Mateo');

insert into b values (1,'TYPE','PUBLIC'); insert into b values (1,'POINTS','17');

insert into b values (2,'LOC','Burlingame'); insert into b values (2,'POINTS','14');

So that's the data inserted. Here is what it looks like:

select *
from a,

       b
where a.id = b.id
order by a.id;

        ID NAME ID CODE DESCRIP
---------- ---------- ---------- ---------- ----------

         1 ITEM1               1 LOC        San Mateo
         1 ITEM1               1 TYPE       PUBLIC
         1 ITEM1               1 POINTS     17
         2 ITEM2               2 LOC        Burlingame
         2 ITEM2               2 POINTS     14

Now... what I want to do is to return all rows from A and show the corresponding row from B for the CODE=TYPE (where it exists but show a null value for CODE if there is no record on B with CODE='TYPE'). I had *thought* the following query would work:

select *
from a,

       b
where a.id = b.id (+)
and nvl(b.code,'TYPE') = 'TYPE'
order by a.id;

        ID NAME ID CODE DESCRIP
---------- ---------- ---------- ---------- ----------

         1 ITEM1               1 TYPE       PUBLIC
         3 ITEM3

But it only returns rows from A where there are NO rows on B for the specified ID. How should I code the query to return the results (mockedup)  below?

        ID NAME ID CODE DESCRIP
---------- ---------- ---------- ---------- ----------

         1 ITEM1               1 TYPE       PUBLIC
         2 ITEM2
         3 ITEM3

Could be I am tired - am sure this is really straightforward.

-- 
jeremy

Received on Sun Oct 19 2008 - 12:42:21 CDT