Re: Outer join problem

Yikes!!

That gives me a horrendous cartesian product or something..

It's definitely the "AND seqID" part of the query that is screwing up.

I guess the equivalent oracle demo (using emp and dept tables) would be something along the lines of:

SELECT e.sal, d.location, e.hiredate
from emp e, dept d
where e.deptno(+)=d.deptno
and e.sal='1500'
/

Perhaps a subquery of some sort is the answer..? Thanks,

Sarah

david_g wrote:In article <38A01BF8.E52365FD_at_NOSPAMTHANX.hotmail.com>, "s. hunter"
<sarahxhunter_at_NOSPAMTHANX.hotmail.com> wrote:
> Hi,
> I *know* I should be able to figure this out but my brain hurts!!!
> I have 2 tables in a database, A and B.
> Table A has columns called DistID (PK), SeqID (an FK from another
> table,
> C), CatID (FK from table B) and numerical_column.
> Table B has 2 columns: CatID (PK) and a description column.
> I thought that my query would look like this:
> SELECT a.seqid, b.description, a.numerical_column
> from a, b
> where a.catid(+)=b.catid
> and a.seqid='12345'
> order by b.description
> /
> because I want the query to return the amount in the numerical
> column as
> null if the catID isn't present in table A, together with the rest
> of
> the query results.
> Does this make sense. I was sure that an outer join should work?!
> Or do I need to use a UNION statement instead?
> Thanks,
> Sarah

try this:
SELECT a.seqid, b.description, a.numerical_column from a, b
where a.catid(+)=b.catid
and nvl(a.seqid,'12345')='12345'
order by b.description
/ Received on Tue Feb 08 2000 - 10:05:11 CST