Received: from web20802.mail.yahoo.com (web20802.mail.yahoo.com [216.136.226.191]) by postgresql.org (8.11.3/8.11.4) with SMTP id g0HK13V82650 for ; Thu, 17 Jan 2002 15:01:04 -0500 (EST) (envelope-from jeff_eckermann@yahoo.com) Message-ID: <20020117200103.3742.qmail@web20802.mail.yahoo.com> Received: from [65.11.165.34] by web20802.mail.yahoo.com via HTTP; Thu, 17 Jan 2002 12:01:03 PST Date: Thu, 17 Jan 2002 12:01:03 -0800 (PST) From: Jeff Eckermann Subject: Re: Union instead of Outer Join To: Unnikrishnan Menon , pgsql-sql@postgresql.org In-Reply-To: <00d101c19f6d$8eba7300$e90aa8c0@UNNIKRISHNAN> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Archive-Number: 200201/201 X-Sequence-Number: 6287 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 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/