Re: left outer join

Thanks. I finally found the answer from an extensive web search. You need to add the (+) to all filters. The correct join syntax requires the (+) on attendance.UID also:

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

In article <96353r$30o$6_at_lure.pipex.net>,   "Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote:
> for point 2 try
>
> SELECT events.name, NVL(attendance.attended,N,attendance.attended)
> FROM events, attendance
> WHERE events.eventid = attendance.eventid(+)
> AND attendance.UID='SOMEID'
>
> if your query is not returning the exepected result set then its
 likely
> you've put the (+) on the wrong side.
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
>
> <brianjh_deja_at_my-deja.com> wrote in message
> news:960tpb$t9i$1_at_nnrp1.deja.com...
> > 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/
>
>

Sent via Deja.com
http://www.deja.com/ Received on Sat Feb 10 2001 - 12:34:12 CST