-
join problems
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!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|