|
|
Posted by Hilarion on 09/09/05 15:29
> I'm not sure if I'm going about this the sensible way for not, so...
>
> I have 2 tables. Once contains details of various events. Name of the event,
> when it starts, how many days and a primary key.
>
> Another table has the names of people going to the events, and a key that
> corresponds to an event in the first table.
>
> I want to write a query that will produce a recordset that has the name of
> the event and the number of people attending it.
>
> So basically name of event, then count(query to find people attending event
> x)
>
> I could do this in PHP by created a nested sql query, then use array push to
> add that data into a bigger array, then output that. Which is what I've
> done. But I was hoping to be able to write an SQL query so I could then use
> the ADODB function to output to a CSV file.
>
> Is it possible to write an SQL query to do what I've tried to explain above,
> or should I just stick with PHP and write the functions to export as CSV.
>
> Any suggestions? My MYSQL query skills are limited, but I'm trying to expand
> them. This seemed a good place to start.
To get count of people for each event do something like this:
SELECT event_table.event_id, event_table.event_name, count( people_table.person_id ) AS cnt
FROM event_table LEFT JOIN people_table ON event_table.event_id = people_table.event_id
GROUP BY event_table.event_id, event_table.event_name
ORDER BY event_table.event_name, event_table.event_id
To get a count of people for one event you could add WHERE clause to the query
above (after FROM clause and before GROUP BY clause) or use one query to
get the event data and another to get the count:
SELECT count(*) AS cnt
FROM people_table
WHERE event_id = $event_id
If you want to use the first query and get more data from the event_table, then
don't forget to put the additional column names in GROUP BY clause (it's probably
not needed in MySQL, but will make the query work also in other SQL engines
and should be less dependant on the table structure).
Hilarion
Navigation:
[Reply to this message]
|