Thread: Union instead of Outer Join
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
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/
On Thu, Jan 17, 2002 at 09:12:17PM +0530, Unnikrishnan Menon <unnikrishnan.menon@chennai.transys.net> 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.