Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> left outer join

left outer join

From: <brianjh_deja_at_my-deja.com>
Date: Thu, 08 Feb 2001 22:10:49 GMT
Message-ID: <95v5h3$gum$1@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/ Received on Thu Feb 08 2001 - 16:10:49 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US