Re: left outer join

try this:

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

brianjh_deja_at_my-deja.com schrieb:

> 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/
  Received on Tue Feb 27 2001 - 03:03:22 CST