two table query -- one being a count

[cgraz]

I have a query that looks like this:

SELECT *, DATE_FORMAT(date, "%M %D %Y") AS mydate FROM events_entry ORDER BY date DESC, event ASC');

But I also have a second table called events_rsvp that holds the people who have rsvp'd for the events along with their info. events_rsvp has a field called event_id that equals the id field in events_entry to match each person's reservation with the event.

I am just trying to do a query and get the amount of people that have rsvp'd for each event (so if id on events_entry equals 5, then get the number of entries in the events_rsvp table with event_id equaling 5, then move onto the next id).

I've played around with some SQL statements and looked into joins but haven't been able to quite get it.

Thanks,

Cgraz

[Drakla]

select count(*) FROM table1, rsvp WHERE table1.eventid=rsvp.eventid AND eventid = 5;

Something along the lines of that.

[cgraz]

hey first off thank you for your help. But that's not quite what I was going for. Let me try to clarify.

I have two tables

events_entry (Holds the events)

id
event
location
date
etc

events_rsvp (holds all the registered people for all the events)

id
event_id (matches the id field in events_entry)
contact_name
title
email
etc

What I want to do is create a listing of events, and before each event, write how many people are registered for that event.

(10) Event Title
Event info

(2) Event Title 2
Event info

where (10) and (2) are the number of people registered for their respective events. I want to query events_entry for all the events, and when it's pulling each individual event, I want it to also grab the amount of records in events_rsvp that have the same id in events_id

I tried this:

SELECT *
FROM events_entry INNER JOIN events_rsvp ON
events_entry.id=events_rsvp.event_id

But that pulls all the information from all the tables, and only if someone has RSVP'd. Some of those counts are going to say zero, and I don't want to pull all the information from both databases. Just the events_entry database, and in each row pulled from MySQL, I want a column called registered that gives me the (10) and the (2) in the example above.

I hope that makes more sense. Thanks for all your help.

Cgraz