Re: please help with a query

Harry wrote:
> I have two oracle 9i tables on Win2003 server.
> For simplicity, let say:
> Table A contains two fields: A.id, A.part_id.
> Table B contains two fields: B.part_id, B.desc.
>
> When I ran the following sqlplus query,
>
> select
> A.id, B.desc
> from
> A, B
> where
> A.part_id = B.part_id
> and A.part_id <= 20;
>
> I got some entries returned. So far so good.
> E.G.
> id Desc
> ---- ----
> 5 head
> 10 chest
>
> What I want is an sql which will return the following results.
>
> id Desc
> --- ----
> 5 head
> 10 chest
> 15 <empty> <-- when no B.part_id corresponds to A.part_id
> 20 <empty> '' ''
>
>
> How should I formulate such an query?
>
> TIA
>

Non ANSI:

select A.id,B.desc
from A,B
where A.part_id=B.part_id(+)
and A.part_id <= 20;

ANSI select A.id,B.desc
from A left outer join B on (A.part_id=B.part_id) where A.part_id<=20;

Best Regards

Maxim Received on Fri Jul 22 2005 - 15:12:15 CDT