SELECT, COUNT question

[dcowdan]

This problem involves three of my database tables:

user- a list of all users of web app

event - a list of events users can sign up for

eventdet- table with two columns: userid and eventid. intended to keep track of who has signed up for which event.

Here's the question: I want to display a web page that will list all events and the number of users currently signed up for each one.

My query at the moment is this:

SELECT eventid,eventtitle FROM event;

I want to end up with something like this:

SELECT eventid,eventtitle,COUNT([SELECT * FROM eventdet WHERE event.eventid = eventdet.eventid])

Any advice?

--
All table and column names have been changed to protect the innocent.

[bubblenut]

SELECT event.eventid as eventid, event.evettitle, COUNT(eventdet.evetid) FROM event, eventdet WHERE event.eventid=eventdet.eventid GROUP BY eventdet.eventid

HTH
Rob

[dcowdan]

Thanks, that put me on the right track. I had one problem with the query you supplied: any event.eventid that had no counterparts in the eventdet.eventid field would not be included in the results. I solved this with the following:

SELECT event.eventid, event.eventtitle, COUNT( eventdet.eventid )
FROM event
LEFT JOIN eventdet ON event.eventid = eventdet.eventid
GROUP BY event.eventid( eventdet.eventid )

Again, thanks for the help

[dcowdan]

:eek: I made a typo above. It should read:
SELECT event.eventid, event.eventtitle, COUNT( eventdet.eventid )
FROM event
LEFT JOIN eventdet ON event.eventid = eventdet.eventid
GROUP BY event.eventid