Re: Going mad - outer join problem

Jeremy schrieb:
> 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 (mocked-
> up) 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.
>
>
>

select *
from a,

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

Best regards

Maxim Received on Sun Oct 19 2008 - 13:17:06 CDT