Re: SQL question on an outer join

From: fergus <fergus_vr01_at_yahoo.com>
Date: Sat, 10 May 2008 09:02:53 -0700 (PDT)

On May 10, 12:18 am, Pat <pat.ca..._at_service-now.com> wrote:
> On May 9, 8:40 pm, fergus_v..._at_yahoo.com wrote:
>
>
>
>
>
> > Hello,
> >  Learning SQL - will appreciate any help.
> >  Here is the case:
>
> > Two tables with 0..n relationship
> > TableA                  TableB
> > -----------             --------------
> > ColA                    ColB
> >                            ColA_FK
> >                            Col_C
>
> > Data
> > TableA.ColA
> > ====================
> >  1
> >  2
>
> > Table B
> > ColB   ColA_FK    Col_C
> >  11      2              12345
> >  12      2              99999
>
> >  Resultset:
>
> >  --------------------------------------
> >  ColA   ColB      Col_C
> >  --------------------------------------
> >  1       -        -
> >  2       12       99999
>
> >  In case no data exists in TableB for ColA=11,  1 from TableA shows up
> > in result without any data from TableB
> >  However, in case of 2 from ColA,  we want to record from TableB with
> > Col_C = 99999
>
> > I can do the outer join - however, how do I limit it so it picks only
> > the row with 9999?
>
> > Fergus
>
> select tablea.cola, tableb.colb, tableb.colc from tablea left join
> tableb on tablea.cola = tableb.cola_fk where tableb.colc = 9999- Hide quoted text -
>
> - Show quoted text -

First of all, thanks for your input.
However, the query you posted does not seem to be producing the correct result set per my question.

Here is the test case:

create table tableA (colA number not null);  create table tableB (colB number not null, colA_fk number_not null, colC number not null);
insert into tableA values (1);
insert into tableA values (2);
commit;
insert into tableB values (11,2,12345);  insert into tableB values (12,2,99999);  commit;

select tableA.colA, tableB.colA_fk, tableB.colC from tableA left join tableB on tableA.colA = tableB.colA_fk where tableB.colC = 99999;

ColA ColA_FK ColC

2 12 99999

I would also like to display another row:  1 - -
in there.

Thanks
-- Fergus Received on Sat May 10 2008 - 11:02:53 CDT

Original text of this message