Count of a ID in another database

[danielkessler]

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

[danielkessler]

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