Count of a ID in another database
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Count of a ID in another database

  1. #1
    Join Date
    Sep 2004
    Posts
    51

    Count of a ID in another database

    I am grabbing a list of records from a registration table. Of that list, I want to find out how many times each item appears in the attendance table, with some other qualifiers at the same time.

    My try has been to get the list of records from the registration table as a sub-query with each item having a passport_listing_id. Then in the uber-query, I pull out the same list of fields and add another field that does another select just for the count that the listing appears in the attendance table. In that count query, I don't know how to designate the passport_listing_id that I gained in the subquery.

    Oy.

    Here's what I have and the item that I can't figure is designated with a 'xxxxxxxx':

    SELECT passport_registration_id,passport_listing_id,title,start_date,
    (
    SELECT COUNT (passport_listing_id)
    FROM passport_attendance
    WHERE people_id = #session.user.id#
    AND passport_listing_id = xxxxxxx
    ) AS attendance_count
    FROM (
    SELECT a.passport_registration_id,b.passport_listing_id,b.title,b.start_date
    FROM passport_registration a, passport_listing b
    WHERE a.passport_listing_id = b.passport_listing_id
    AND b.end_date < #now()# AND b.status='display'
    AND a.s_uid = #session.user.id#
    ORDER BY b.start_date
    )



    I hope this makes sense.

    daniel

  2. #2
    Join Date
    Sep 2004
    Posts
    51
    It appears that I needed to assign the sub-query to a variable that that will clarify it's designation in the COUNT. This is the way that it worked out for me.


    SELECT passport_registration_id,passport_listing_id,title,start_date,
    (
    SELECT COUNT (passport_listing_id)
    FROM passport_attendance
    WHERE people_id = #session.user.id#
    AND passport_listing_id = g.passport_listing_id
    ) AS attendance_count,
    (
    SELECT COUNT (passport_listing_id)
    FROM passport_survey
    WHERE people_id = #session.user.id#
    AND passport_listing_id = g.passport_listing_id
    ) AS survey_count
    FROM (
    SELECT a.passport_registration_id,b.passport_listing_id,b.title,b.start_date
    FROM passport_registration a, passport_listing b
    WHERE a.passport_listing_id = b.passport_listing_id
    AND b.end_date < #now()# AND b.status='display'
    AND a.s_uid = #session.user.id#
    ORDER BY b.start_date DESC
    ) g

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