From unnikrishnan.menon@chennai.transys.net Thu Jan 17 15:39:59 2002 Received: from chennai.transys (chennai.transys.net [12.33.140.105]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0HFdwV15898 for ; Thu, 17 Jan 2002 10:39:59 -0500 (EST) (envelope-from unnikrishnan.menon@chennai.transys.net) Received: from UNNIKRISHNAN ([192.168.10.233]) by chennai.transys with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2650.21) id CGH4ZFWR; Thu, 17 Jan 2002 21:22:39 +0530 Message-ID: <00d101c19f6d$8eba7300$e90aa8c0@UNNIKRISHNAN> From: "Unnikrishnan Menon" To: Subject: Union instead of Outer Join Date: Thu, 17 Jan 2002 21:12:17 +0530 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00CE_01C19F9B.A4FF5A30" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200201/200 X-Sequence-Number: 6286 This is a multi-part message in MIME format. ------=_NextPart_000_00CE_01C19F9B.A4FF5A30 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I have this query in oracle which I'am trying to port to PostgreSQL : Select=20 item_code, nvl(user_id,' ') as user_id=20 from=20 item_list L, User_detail B, User_detail S where=20 L.user_id =3D S.User_id and L.other_userid =3D B.user_id(+) and L.item_code =3D 1234; This query gives me 1 row as result. L.other_userid could be null. I try changing the query thus in postgreSQL : Select=20 item_code, nvl(user_id,' ') as user_id=20 from=20 item_list L, User_detail B, User_detail S where=20 L.user_id =3D S.User_id and L.other_userid =3D B.user_id and L.item_code =3D 1234 Union Select=20 item_code, nvl(user_id,' ') as user_id=20 from=20 item_list L, User_detail B, User_detail S where=20 L.user_id =3D S.User_id and L.item_code =3D 1234 and 0 =3D ( Select=20 count(*)=20 from=20 listed_items L, user_detail B, user_detail S where=20 L.other_userid =3D 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 ------=_NextPart_000_00CE_01C19F9B.A4FF5A30 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi,
 
I have this query in oracle which I'am try= ing to=20 port to PostgreSQL :
 
Select
    item_code, nvl(user_id,= ' ') as=20 user_id
from
    item_list L, User_detai= l B,=20 User_detail S
where
    L.user_id =3D S.User_id= =20 and
    L.other_userid =3D B.us= er_id(+)=20 and
    L.item_code =3D 1234;
 
This query gives me 1 row as result. L.oth= er_userid=20 could be null.
 
I try changing the query thus in postgreSQ= L=20 :
 
Select
    item_code, nvl(user_id,= ' ') as=20 user_id
from
    item_list L, User_detai= l B,=20 User_detail S
where
    L.user_id =3D S.User_id= =20 and
    L.other_userid =3D B.us= er_id=20 and
    L.item_code =3D 1234
Union
Select
    item_code, nvl(user_id,= ' ') as=20 user_id
from
    item_list L, User_detai= l B,=20 User_detail S
where
    L.user_id =3D S.User_id= =20 and
    L.item_code =3D 1234 and
    0 =3D ( Select
           &nbs= p;    =20 count(*)
            from
           &nbs= p;     listed_items=20 L, user_detail B, user_detail S
            where
              &= nbsp;=20 L.other_userid =3D B.user_id);
 
The above query does not return any row. Where could I be going=20 wrong?
 
Any help would be appreciated.
 
Thanx in advance
 
Unni
------=_NextPart_000_00CE_01C19F9B.A4FF5A30-- From bruno@wolff.to Thu Jan 17 19:50:27 2002 Received: from wolff.to (wolff.to [66.92.219.49]) by postgresql.org (8.11.3/8.11.4) with SMTP id g0HJoRV78636 for ; Thu, 17 Jan 2002 14:50:27 -0500 (EST) (envelope-from bruno@wolff.to) Received: (qmail 1316 invoked by uid 500); 17 Jan 2002 19:52:15 -0000 Date: Thu, 17 Jan 2002 13:52:15 -0600 From: Bruno Wolff III To: Unnikrishnan Menon Cc: pgsql-sql@postgresql.org Subject: Re: Union instead of Outer Join Message-ID: <20020117195215.GA1260@wolff.to> Mail-Followup-To: Unnikrishnan Menon , pgsql-sql@postgresql.org References: <00d101c19f6d$8eba7300$e90aa8c0@UNNIKRISHNAN> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <00d101c19f6d$8eba7300$e90aa8c0@UNNIKRISHNAN> User-Agent: Mutt/1.3.25i X-Archive-Number: 200201/202 X-Sequence-Number: 6288 On Thu, Jan 17, 2002 at 09:12:17PM +0530, Unnikrishnan Menon wrote: > Any help would be appreciated. If you are running 7.1 or can upgrade to 7.1, then you can do outer joins and don't need to use the union trick. From jeff_eckermann@yahoo.com Thu Jan 17 20:01:04 2002 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/