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 my latest attempt:
<CFQUERY NAME="passport_registration2" DATASOURCE="dpch">
SELECT a.passport_listing_id, a.passport_registration_id,
a.email_sent_attendance,a.s_uid, a.student_id,
a.people_id,d.passport_attendance_id
FROM passport_registration a,passport_attendance d
WHERE a.s_uid (+) = d.s_uid
AND d.passport_listing_id = #url.passport_listing_id#
ORDER BY s_uid ASC
</CFQUERY>
It's actually much more complicated, but this is the simplified query. Just
for grins, here's the whole query that I started with and am trying to add the
passport_attendance_id to (this works fine until I try to add
passport_attendance_id:
<CFQUERY NAME="passport_registration" DATASOURCE="dpch">
SELECT
a.passport_listing_id,a.passport_registration_id,a.email_sent_attendance,a.s_uid,a.student_id,a.people_id,
CASE WHEN a.student_id is not null THEN b.fname ELSE
c.fname END fname,
CASE WHEN a.student_id is not null THEN b.lname ELSE
c.lname END lname,
CASE WHEN a.student_id is not null THEN b.email ELSE
c.email END email
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#
</CFQUERY>
thanks for any assistance.
daniel
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327766
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
