Re: SQL question on an outer join

On May 10, 12:18=A0am, Pat <pat.ca...@[EMAIL PROTECTED]
> wrote:
> On May 9, 8:40 pm, fergus_v...@[EMAIL PROTECTED]
 wrote:
>
>
>
>
>
> > Hello,
> > =A0Learning SQL - will appreciate any help.
> > =A0Here is the case:
>
> > Two tables with 0..n relation****p
> > TableA =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0TableB
> > ----------- =A0 =A0 =A0 =A0 =A0 =A0 --------------
> > ColA =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0ColB
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0ColA_FK
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Col_C
>
> > Data
> > TableA.ColA
> > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> > =A01
> > =A02
>
> > Table B
> > ColB =A0 ColA_FK =A0 =A0Col_C
> > =A011 =A0 =A0 =A02 =A0 =A0 =A0 =A0 =A0 =A0 =A012345
> > =A012 =A0 =A0 =A02 =A0 =A0 =A0 =A0 =A0 =A0 =A099999
>
> > =A0Resultset:
>
> > =A0--------------------------------------
> > =A0ColA =A0 ColB =A0 =A0 =A0Col_C
> > =A0--------------------------------------
> > =A01 =A0 =A0 =A0 - =A0 =A0 =A0 =A0-
> > =A02 =A0 =A0 =A0 12 =A0 =A0 =A0 99999
>
> > =A0In case no data exists in TableB for ColA=3D11, =A01 from TableA
show=
s up
> > in result without any data from TableB
> > =A0However, in case of 2 from ColA, =A0we want to record from TableB
wit=
h
> > Col_C =3D 99999
>
> > I can do the outer join - however, how do I limit it so it picks only
> > the row with 9999?
>
> > Thanks in advance.
> > Fergus
>
> select tablea.cola, tableb.colb, tableb.colc from tablea left join
> tableb on tablea.cola =3D tableb.cola_fk where tableb.colc =3D 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 =3D tableB.colA_fk where tableB.colC =3D 99999;

  --------------------------------------
  ColA   ColA_FK      ColC
  --------------------------------------
  2       12       99999

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

Thanks
-- Fergus