SQL Woes

[Moredhel]

I've become a tad stumped with a little SQL issue.

I have 3 tables, a table of quotes, a table of votes and a table of comments. I need a query that pulls out list of quotes, pulls out the sum and count of votes for each one of those quotes, and the number of comments for each quote. My table structure looks like this:

qdb_comments
---------------------------------------------------------------
| id | quoteid | name | website | comment | postdate | ipaddr |
---------------------------------------------------------------

qdb_votes
--------------------------------
| id | quoteid | vote | ipaddr |
--------------------------------

qdb_quotes
-----------------------------------------------------
| id | content | note | email | postdate | approved |
-----------------------------------------------------

And my current query is this:

SELECT q.id, q.content, q.note, q.postdate, q.approved, COUNT(v.id) AS votes, CAST(SUM(v.vote) AS SIGNED) AS score, COUNT(c.id) AS comments FROM qdb_quotes q LEFT OUTER JOIN qdb_votes v ON v.quoteid = q.id LEFT OUTER JOIN qdb_comments c ON c.quoteid = q.id WHERE q.approved = 1 GROUP BY q.id ORDER BY q.id DESC LIMIT 0, 10;

The problem is that while it's pulling out the votes data fine, the number of comments it pulls out is either 0 if there are no comments, or the number of votes if there are any comments at all. For some reason as soon as there's a single comment it uses the number of votes as the count. I know this is something to do with the way I've done the joins, but I can't work out how else I could do it without a lot of extra queries. Unfortunately the DBMS I'm stuck with is MySQL 4.0.24.

 

[Moredhel]

The distinct worked a treat dude, thanks.

And yeah, I would have thought you would need the group by on the non aggregate fields, you certianly would do on MSSQL, But it looks like MySQL either does that automagically, or just doesn't care.

Thanks again.

 

[Moredhel]

Ach, you can't use DISTINCT inside a SUM(), so it's still counting the multiple rows, leading to such interesting things as a quote with 12 votes and a score of 16.

I hear what you're saying about reworking the query, but I just can't think how else you would do it short of having a query for each quote in your result set, turning one query into lots. Maybe that would end up more efficient as then it would just be simple counts? I can think of a dozen ways of doing it in T-SQL, but MySQL is just so limited.

 

[Moredhel]

Alas, no sub selects in the version of mysql i have to work with. :/

[edit] Although it works perfectly on my dev server here at home on MySQL 5.x, thanks. [/edit]