Re: Going mad - outer join problem

From: David Portas <REMOVE_BEFORE_REPLYING_dportas_at_acm.org>
Date: Sun, 19 Oct 2008 21:44:31 +0100
Message-ID: <H72dnXW6rZKyAWbVnZ2dnUVZ8uidnZ2d@giganews.com>


"Jeremy" <jeremy0505_at_gmail.com> wrote in message news:MPG.236581d7b292f2b5989689_at_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 (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.
>
>
>
> --
> jeremy

SELECT a.id, a.name, b.id, b.code, b.descrip FROM a
LEFT JOIN b
ON a.id = b.id
AND b.code = 'TYPE'
ORDER BY a.id;

-- 
David Portas
Received on Sun Oct 19 2008 - 15:44:31 CDT

Original text of this message