Select all records from one table, and count records in another table

[markrawlingson]

Hopefully someone can help me out with this, it's driving me nuts...

I have two tables - We'll call them table1 and table2. Table1 holds event information, and table2 holds user registration data pertaining to each event in table1. So for each record in Table1 there could be hundreds of records pertaining to that record in Table2 - I am trying to count those records (to reveal the number of people registered for the event held in table1)

I am trying to construct an SQL query which will select ALL events from table1, and COUNT the number of records/registrants pertaining to each event returned in the first select statement.

I know it is possible, and easier, to create a recordset for Table1 and then count the records from table2 each time you loop through the first recordset - but the problem is the two tables have over 340 columns in each (Yeah, I know.. it's mad.. I didn't do it!), so opening and closing a recordset for each loop through table1 is out of the question. We're actually currently doing that and the page takes forever to load (about 1 second per record, so if you have 300 events.. you're waiting about 5 minutes each time you load this page) - thusly I am trying to speed it up by selecting all the events from table1, and counting the registrations for that event in a single select statement.. so that there is no need to continually open and close a recordset for each loop through Table1 records.

I'm sure a subquery of some sort is in order here, but can't get it right, tried a million things.. and notta!

Hope someone can help!
Sincerely,
Mark

[ck9663]

Hopefully someone can help me out with this, it's driving me nuts...

I have two tables - We'll call them table1 and table2. Table1 holds event information, and table2 holds user registration data pertaining to each event in table1. So for each record in Table1 there could be hundreds of records pertaining to that record in Table2 - I am trying to count those records (to reveal the number of people registered for the event held in table1)

I am trying to construct an SQL query which will select ALL events from table1, and COUNT the number of records/registrants pertaining to each event returned in the first select statement.

I know it is possible, and easier, to create a recordset for Table1 and then count the records from table2 each time you loop through the first recordset - but the problem is the two tables have over 340 columns in each (Yeah, I know.. it's mad.. I didn't do it!), so opening and closing a recordset for each loop through table1 is out of the question. We're actually currently doing that and the page takes forever to load (about 1 second per record, so if you have 300 events.. you're waiting about 5 minutes each time you load this page) - thusly I am trying to speed it up by selecting all the events from table1, and counting the registrations for that event in a single select statement.. so that there is no need to continually open and close a recordset for each loop through Table1 records.

I'm sure a subquery of some sort is in order here, but can't get it right, tried a million things.. and notta!

Hope someone can help!
Sincerely,
Mark

lots of ways...

among them:

select table2.eventid, table1.eventname, count(*) 
from table2 inner join table1 on table1.eventid = table2.eventid
group by table2.eventid, table1.eventname
 

[markrawlingson]

Hi, thanks for your suggestion!

I tried that - was the first thing i tried actually, it didn't give the results I wanted so i tried numerous other things before posting here... What was happening is the recordset would return a list of events, and duplicate the event in the record set for each registrant. So if an event had 100 registrants, it would return "event information : registration count = 1" 100 times. So 100 records; displayed on the page it looks like 100 events when it's not really... plus the registrant count would have been totally off. It would look like below.. (when I used that suggest query)

Record 1 Event 1 : registration Count 1
Record 2 Event 1 : registration Count 1
Record 3 Event 1 : registration Count 1
Record 4 Event 1 : registration Count 1
Record 5 Event 1 : registration Count 1
Record 6 Event 1 : registration Count 1
Record 7 Event 1 : registration Count 1
Record 8 Event 1 : registration Count 1
Record 9 Event 1 : registration Count 1
Record 10 Event 1 : registration Count 1
etc, etc

Instead of the desired result, which of course is...

Record 1 Event 1 : registration Count 10

The problem was that I wasn't grouping on the correct field. I grouped on the "event code" field on table1 and it's now giving me almost entirely satisfactory results!

My problem now is that if an event has NO registrants - the record is NOT being returned in the record set. I still want it to, but have the count say 0.

I'm gonna play with it a bit more here, it's probably a simple tweak somewhere... but any suggestions would be appreciated.

Thanks again!

Sincerely,
Mark

[markrawlingson]

Never mind! I used a FULL OUTER JOIN instead of the inner join to grab data from table2 (the registrants table). That data is returned as null if there are no registrants of course, and therefore I know that there are 0 registrants if I check the columns from the second table and they're null.

Thanks a lot for your help.

Sincerely,
Mark

[ck9663]

INNER join was used assuming there's at least 1 participant per event.

[markrawlingson]

Yeah, there will be at least 1 participant for each event, but the registrant may not have come and registered yet.. so in that case the event wouldn't be displayed in the list of events, I've compensated for that but now I have a bigger problem... and much more complicated.

A registration has a status attatched to it. 'OK' for completed, and 'Pending' for a registration who has come to register but hasn't paid, etc. When counting the registrations for an event, I have put a clause in there stating only to count the registrations which are status = 'OK'. Now here's the problem.. If I have an event in the events table, and a registrant is listed in the registrants table but the status is 'Pending' - unless there is at least one other record pertaining to the parent event with status = 'OK' the event itself is not returned in the recordset. Right now, as an example, I am working with an event that has 1 person registered, but their registration is in pending status.. and because I am saying where registrantTbl.Status = 'ok' OR registrantTbl.ID Is Null - neither of those conditions being true - the record is not returned, and the parent record, or the event which the registration belongs to is also not returned - but i still want it to.. but have the registration count be 0.

Here is my sql statement, modified of course to remove sensitive table/column names etc. (these are fake names, don't worry admins!)

SELECT EventTbl.Column1,EventTbl.Column2, Count(*) AS RegCount FROM RegistrantTbl FULL OUTER JOIN EventTbl ON RegistrantTbl.Identifier = EventTbl.Identifier WHERE (RegistrantTbl.Status = 'OK' OR RegistrantTbl.ID Is Null) AND LOWER(EventTbl.Identifier) = '" & LCase(IdentifierInCode) & "' AND EventTbl.FormType = 'EVENT REGISTRATION' GROUP BY EventTbl.Identifier

I've run into a brick wall with this. I don't think it's possible, is it? If I return a count to the registrant table without telling it not to count registration without an 'OK' status then the registration count is wrong and the grouping even gets all screwed up and somewhat of a cartesian product is returned. On the other hand if i DO tell it to ignore registrations without an 'OK' status the registration count is correct but events of this very particular circumstance are not returned.

Anyway, I was hoping you could look it over and see if there's anything I could tweak a little bit to get the results I want - or maybe suggest an alternate method of doing this that will..

Thanks a lot!

Sincerely,
Mark

[sunish]

please try like this

SELECT master_adverse_event.adverse_event_id, master_adverse_event.adverse_event_name,SUM(IF(transaction_adverse_event.adverse_event_id IS NULL, 0, 1)) AS total FROM master_adverse_event LEFT JOIN transaction_adverse_event ON master_adverse_event.adverse_event_id = transaction_adverse_event.adverse_event_id WHERE master_adverse_event.adverse_event_id IN(1, 2, 3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19) GROUP BY master_adverse_event.adverse_event_id, master_adverse_event.adverse_event_name

@markrawlingson