pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Union instead of Outer Join
3+ messages / 3 participants
[nested] [flat]

* Union instead of Outer Join
@ 2002-01-17 15:42  Unnikrishnan Menon <unnikrishnan.menon@chennai.transys.net>
  0 siblings, 2 replies; 3+ messages in thread

From: Unnikrishnan Menon @ 2002-01-17 15:42 UTC (permalink / raw)
  To: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Union instead of Outer Join
@ 2002-01-17 19:52  Bruno Wolff III <bruno@wolff.to>
  parent: Unnikrishnan Menon <unnikrishnan.menon@chennai.transys.net>
  1 sibling, 0 replies; 3+ messages in thread

From: Bruno Wolff III @ 2002-01-17 19:52 UTC (permalink / raw)
  To: Unnikrishnan Menon <unnikrishnan.menon@chennai.transys.net>; +Cc: pgsql-sql

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.



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Union instead of Outer Join
@ 2002-01-17 20:01  Jeff Eckermann <jeff_eckermann@yahoo.com>
  parent: Unnikrishnan Menon <unnikrishnan.menon@chennai.transys.net>
  1 sibling, 0 replies; 3+ messages in thread

From: Jeff Eckermann @ 2002-01-17 20:01 UTC (permalink / raw)
  To: Unnikrishnan Menon <unnikrishnan.menon@chennai.transys.net>; pgsql-sql

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/



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2002-01-17 20:01 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-01-17 15:42 Union instead of Outer Join Unnikrishnan Menon <unnikrishnan.menon@chennai.transys.net>
2002-01-17 19:52 ` Bruno Wolff III <bruno@wolff.to>
2002-01-17 20:01 ` Jeff Eckermann <jeff_eckermann@yahoo.com>

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