SQL Woes

Moredhel · 11 Mar 2006 at 15:26

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:

Code:

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:

Code:

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.

Haircut · 11 Mar 2006 at 17:43

Putting DISTINCT in your aggregate fields should solve the problem, so you would have COUNT(DISTINCT v.id) AS votes and the same for the other two.

As you said the problem is down the way the joins are done.
You are joining on quoteid, so if you have a quote that has 10 comments and 6 votes you are returning 60 rows for that quote in your query.
You don't see this because of the GROUP BY.

One thing I would always suggest when doing a GROUP BY query is to see the results without the GROUP BY in there just so you can check that the raw data is right first.
I would want to change the query around a bit rather than just use the DISTINCT as I suggested above as that's a very expensive option and that query could take a long time to execute the way it stands at the minute id there are quotes with many votes and comments.

Also, surely you need to put the other non-aggregate columns in the GROUP BY for that query to work :confused:

Moredhel · 11 Mar 2006 at 17:57

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 · 11 Mar 2006 at 21:09

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.

Haircut · 11 Mar 2006 at 22:30

Does this work in MySQL?

Code:

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

This splits the comment count into an inline view so you just get back a single row per quote in the main query.
If the database lets you do this then it should work.

Moredhel · 12 Mar 2006 at 09:28

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]