join problems

[danielkessler]

I think I should be doing an outer join. I have a set of data (passport_registration) and I want to add a field from passport_attendance. passport_registration is a total set because it's the people that registered for the event. passport_attendance is a subset of those that attended the event. I want passport_registration to have the passport_attendance field "passport_attendance_id".

Here's the passport_attendance structure:
create table passport_attendance (
passport_attendance_id NUMBER Primary Key,
date_added date,
person_scanning VARCHAR2(100),
passport_listing_id NUMBER REFERENCES passport_listing(passport_listing_id),
student_id NUMBER REFERENCES students(student_id),
people_id NUMBER REFERENCES people(id),
s_uid VARCHAR2(50),
approved VARCHAR2(20)
)


Here's the query that I'm trying to add it to:
SELECT a.passport_listing_id,a.passport_registration_id,a.email_sent_attendance,a.s_uid,a.student_id,a.peop le_id
FROM passport_registration a, students b, people c
WHERE a.student_id = b.student_id (+)
AND a.people_id = c.id (+)
AND a.passport_listing_id = #url.passport_listing_id#


Here's my latest try that only gives the entries that are also in the passport_attendance data set:
SELECT a.passport_listing_id,a.passport_registration_id,a.email_sent_attendance,a.s_uid,a.student_id,a.peop le_id,d.passport_attendance_id
FROM passport_registration a, students b, people c, passport_attendance d
WHERE a.student_id = b.student_id (+)
AND a.people_id = c.id (+)
AND (a.s_uid = d.s_uid (+) AND a.passport_listing_id = d.passport_listing_id)
AND a.passport_listing_id = #url.passport_listing_id#



Any assistance or explanation is greatly appreciated.

Thanks!

[hrishy]

Hi

Whats the question but ?

[danielkessler]

How do I have the passport_registration data set have the passport_attendance field "passport_attendance_id"?

I keep receiving too many records returned. Here's a page with some data:
http://sph.umd.edu/test/passport_stats.cfm?passport_listing_id=321

I want to update the second set of data with the first set of data's "passport_attendance_id" column. Since passport_attendance is a subset of passport_registered, where there isn't an entry, I'd like null instead.

thanks!

[danielkessler]

got it!


SELECT
a.passport_listing_id
,a.passport_registration_id
,a.email_sent_attendance
,a.s_uid
,a.student_id
,a.people_id
,coalesce(b.fname, c.fname) fname
,coalesce(b.lname, c.lname) lname
,coalesce(b.email, c.email) email
,passport_attendance_id
FROM passport_registration a
Left outer join students b
On a.student_id = b.student_id
Left outer join people c
On a.people_id = c.id
Left outer join passport_attendance d
On a.passport_listing_id = d.passport_listing_id
and a.s_uid = d.s_uid
WHERE a.passport_listing_id = 321