join problems
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: join problems

  1. #1
    Join Date
    Sep 2004
    Posts
    51

    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!

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Whats the question but ?

  3. #3
    Join Date
    Sep 2004
    Posts
    51
    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_sta...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!

  4. #4
    Join Date
    Sep 2004
    Posts
    51
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width