left outer join

left outer join

Post by brianjh_d.. » Sat, 10 Feb 2001 07:10:49



i want to write a query in oracle that returns a list of all events and
a field flagged if a specific attendee attended those events:

example result set:
EventName Attended
My Event1    Y
My Event2    N
My Event3    Y
My Event4    Y

1) How do you include an additional filter for the User ID in the WHERE
clause using the (+) left outer join ORACLE syntax?
2) Can you use the ISNULL function in the SELECT statement to
return 'y' or 'n' based on whether or not the the second table returned
a result set?

SELECT events.name, ISNULL(attendance.attended)
FROM events, attendance
WHERE events.eventid = attendance.eventid(+) AND attendance.UID='SOMEID'

In this statement  the ISNULL syntax does not work.  ALso, if run
without, it will only return records where there is a corresponding
record in attendance with UID='SOMEID'.  It's not a complete left outer
join with that second filter in the where clause.

Sent via Deja.com
http://www.deja.com/

 
 
 

left outer join

Post by Johannes Wah » Wed, 28 Feb 2001 18:03:22


try this:

SELECT
    events.name, DECODE(attendance.attended,NULL,'N','Y')
FROM
    events, attendance
WHERE
    events.eventid = attendance.eventid(+) AND
    attendance.UID='SOMEID'


Quote:> i want to write a query in oracle that returns a list of all events and
> a field flagged if a specific attendee attended those events:

> example result set:
> EventName Attended
> My Event1    Y
> My Event2    N
> My Event3    Y
> My Event4    Y

> 1) How do you include an additional filter for the User ID in the WHERE
> clause using the (+) left outer join ORACLE syntax?
> 2) Can you use the ISNULL function in the SELECT statement to
> return 'y' or 'n' based on whether or not the the second table returned
> a result set?

> SELECT events.name, ISNULL(attendance.attended)
> FROM events, attendance
> WHERE events.eventid = attendance.eventid(+) AND attendance.UID='SOMEID'

> In this statement  the ISNULL syntax does not work.  ALso, if run
> without, it will only return records where there is a corresponding
> record in attendance with UID='SOMEID'.  It's not a complete left outer
> join with that second filter in the where clause.

> Sent via Deja.com
> http://www.deja.com/


 
 
 

1. LEFT OUTER LOOP JOIN and LEFT OUTER JOIN

hi all:
        for a long time, I 've been confused about how to use LEFT OUTER
LOOP JOIN and LEFT OUTER JOIN,
sometimes, when I construct a query with LEFT OUTER JOIN, it's faster than
LEFT OUTER LOOP JOIN, but there is other time,
if I construct a query with LEFT OUTER LOOP JOIN, it's faster than LEFT
OUTER JOIN,  this is a mystery to me,  does anyone know
how to use these JOIN TYPE properly?  like giving some explaination or some
examples?

                                               please help!
                                                            thanks
                                                                        -Jay

2. ASP/ADODB & Btrieve

3. LEFT JOIN and LEFT OUTER JOIN

4. how to startup ORACLE DB under unix

5. varying phone # PICTURE clauses question

6. Left-Outer join and Right-Outer join

7. replicating from AIX sybase to AIX db2

8. Left outer join works, Right outer join just goes on for ever, processor at 100%

9. LEFT OUTER JOIN vs RIGHT OUTER JION

10. LEFT OUTER JOIN faster than INNER JOIN?

11. Left Outer join on Inner join

12. left outer join on Inner join