OPPS...
Change the on a.student_id = d.student_id to on a.passport_listing_id =
d.passport_listing_id
Steve
-----Original Message-----
From: DURETTE, STEVEN J (ATTASIAIT)
Sent: Tuesday, October 27, 2009 3:11 PM
To: cf-talk
Subject: RE: SQL Join help needed
Sorry about that... Try this...
<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
,coalesce(b.fname, c.fname) fname
,coalesce(b.lname, c.lname) lname
,coalesce(b.email, c.email) email
,passport_attendance_id -- Will return null if there
isn't a value.
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.student_id = d.student_id
WHERE a.passport_listing_id = #url.passport_listing_id#
</CFQUERY>
-----Original Message-----
From: daniel kessler [mailto:[email protected]]
Sent: Tuesday, October 27, 2009 2:51 PM
To: cf-talk
Subject: Re: SQL Join help needed
>This code isn't using inner/outer join notation, but the old style. Is
>there a reason for this?
It's the way that I learned to do it. I've read your notation; it's
certainly more explicit.
Further though, the assistance that I needed was adding the field
'passport_attendance_id' from the 'passport_attendance' table. I don't
see that in there and I am having difficulties adding it.
any help on that, please?
Here is the structure of 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)
thanks!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:327773
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
