Sql Logic Question about most efficient query.

[noimad1]

Ok, so here is my situation. I have 100,000+ records stored in my database. All of my users are stored in one table, and a survey they took was in another table.

What i need to do is query the user table to get their ID, then query the survey table where their ID is also stored to get a record from the survey page (who referred them). I need to do this so I can get a count of how many users each referrer sent me.

Now, the way I originaly coded it up was just to query the user table for all records between a certain date, then looped through the results and queried the survey table for each record and then just added up my totals. This worked fine until my tables grew to a large amount of records of course.

Is there a more efficient way to do this, say by using the join command or something like that?

[NogDog]

Sounds like a job for a JOIN and a GROUP BY, something like

SELECT user.id, user.name, COUNT(*) AS `total`
FROM `user`
INNER JOIN `survey` ON survey.user_id = user.id
WHERE user.date_col BETWEEN '2009-01-01' AND '2009-12-31'
GROUP BY user.id

If you don't just want a count of survey rows but a total of some field, the first line would be changed to:

SELECT user.id, user.name, SUM(survey.col_name) AS `total` . . .

[johanafm]

And make sure user.id, user_id and date_col are all indexed.