From: Jeff Eckermann <jeff_eckermann@yahoo.com>
To: Unnikrishnan Menon <unnikrishnan.menon@chennai.transys.net>
To: pgsql-sql@postgresql.org
Subject: Re: Union instead of Outer Join
Date: Thu, 17 Jan 2002 12:01:03 -0800 (PST)
Message-ID: <20020117200103.3742.qmail@web20802.mail.yahoo.com> (raw)
In-Reply-To: <00d101c19f6d$8eba7300$e90aa8c0@UNNIKRISHNAN>
select
item_code, coalesce(user_id, ' ') as user_id
from
item_list l inner join user_detail s on l.user_id
= s.user_id
left outer join user_detail b on l.other_userid =
b.user_id
where
l.item_code = 1234;
Joins can be named in different ways; see the "SELECT"
page in the "SQL Commands" section of the docs.
Your message suggests that you want a right join on b
& l; the syntax above corresponds to what you stated
(per my Oracle reference).
--- Unnikrishnan Menon
<unnikrishnan.menon@chennai.transys.net> wrote:
> Hi,
>
> I have this query in oracle which I'am trying to
> port to PostgreSQL :
>
> Select
> item_code, nvl(user_id,' ') as user_id
> from
> item_list L, User_detail B, User_detail S
> where
> L.user_id = S.User_id and
> L.other_userid = B.user_id(+) and
> L.item_code = 1234;
>
> This query gives me 1 row as result. L.other_userid
> could be null.
>
> I try changing the query thus in postgreSQL :
>
> Select
> item_code, nvl(user_id,' ') as user_id
> from
> item_list L, User_detail B, User_detail S
> where
> L.user_id = S.User_id and
> L.other_userid = B.user_id and
> L.item_code = 1234
> Union
> Select
> item_code, nvl(user_id,' ') as user_id
> from
> item_list L, User_detail B, User_detail S
> where
> L.user_id = S.User_id and
> L.item_code = 1234 and
> 0 = ( Select
> count(*)
> from
> listed_items L, user_detail B,
> user_detail S
> where
> L.other_userid = B.user_id);
>
> The above query does not return any row. Where could
> I be going wrong?
>
> Any help would be appreciated.
>
> Thanx in advance
>
> Unni
>
__________________________________________________
Do You Yahoo!?
Send FREE video emails in Yahoo! Mail!
http://promo.yahoo.com/videomail/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jeff_eckermann@yahoo.com, unnikrishnan.menon@chennai.transys.net
Subject: Re: Union instead of Outer Join
In-Reply-To: <20020117200103.3742.qmail@web20802.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox