pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Unnikrishnan Menon <unnikrishnan.menon@chennai.transys.net>
To: pgsql-sql@postgresql.org
Subject: Union instead of Outer Join
Date: Thu, 17 Jan 2002 21:12:17 +0530
Message-ID: <00d101c19f6d$8eba7300$e90aa8c0@UNNIKRISHNAN> (raw)

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

view thread (3+ messages)  latest in thread

Message-ID: <00d101c19f6d$8eba7300$e90aa8c0@UNNIKRISHNAN>
Permalink:  ../00d101c19f6d$8eba7300$e90aa8c0@UNNIKRISHNAN/
Also on:    postgresql.org/message-id/00d101c19f6d$8eba7300$e90aa8c0@UNNIKRISHNAN

 · 

reply

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: unnikrishnan.menon@chennai.transys.net
  Subject: Re: Union instead of Outer Join
  In-Reply-To: <00d101c19f6d$8eba7300$e90aa8c0@UNNIKRISHNAN>

* 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