Re: SQL question on an outer join
Date: Sat, 10 May 2008 09:02:53 -0700 (PDT)
Message-ID: <c6d98047-a724-4d73-8599-bfb3edd98a09@p25g2000hsf.googlegroups.com>
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?
>
> > Thanks in advance.
> > 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