SQL behaving badly...

[aspen]

SELECT comments.cid, 
       comments.email, 
       comments.name,  
       DATE_FORMAT(comments.date, '%M %D, %Y') as fdate,
       comments.subject, 
       comments.text,
       COUNT(com_replies.rid) AS cou, 
       AVG(com_votes.vote) as avv 
FROM comments 
       LEFT JOIN com_replies on com_replies.cid = comments.cid 
       LEFT JOIN com_votes on com_votes.cid = comments.cid 
WHERE comments.id = '$id' and 
      comments.status = '1' 
GROUP BY comments.cid 
ORDER by avv desc, comments.date 
LIMIT 20

Right.

So this SQL selects from 3 tables. A table of commments, a table of replies to comments, and a table of comment votes.

What I'm doing is building a list of comments along with the rating each comment has and a number of replies for the comment. Its very much like a forum.

Whats not working is the number of replies. If there is no replies it lists the number 0 correctly. If there is a reply it lists a random number.

To see this in action check out this page: http://www.online-literature.com/orwell/1984/

Anything with a reply gets a random number of replies listed.

Each comment at most has like 1 reply. I looked in the database, the random number is neither the ID of the reply or the number of the reply (in order).

So... I have no idea where the number is coming from or how it's getting it.

[m1l]

Just is just a stab in the dark but the only thing I can think is that one of your left joins is causing the problem. I would put decent money on the fact that field 'com_replies.rid' was blank or the same (probably all the same) for the records it's bringing back. You could prove this by removing the count, avg and group by and see what it brings back then.

I don't know what the DB is, looks like MySQL or Access, I'm more of an Oracle person but if the problem is I think, then the fix on oracle would be

COUNT(DISTINCT(com_replies.rid))

This may or may not work in MySQL.

Hope this helps or at least points you in the right direction.

[aspen]

It is MySQL and I know for a fact that you can't do a count distinct like that in MySQL.

To get distinct counts you need to do a count and a group by, which is what I'm doing.

I can remove the extra parts of the query and make a prototype one that does the count and a group by and it works perfectly.

Its only when I put it with the rest of the query that this happens.

com_replies.rid is not going to be blank or identical. Its the primary key of the com_replies table so each one is going to be defined and different.

[m1l]

Looking at this further, is the number returned the number of votes placed ? I say this because I would say that the count will return :-

1 (the comments table will always be unique in this select) * number of comments * number of votes.

You say the number of comments is always one, so I reckon the count it shows will be the number of votes for that comment. If the number of comments was more than one, that would also be added in.

[aspen]

I was thinking that perhaps that was the case. I have yet to confirm it though.

Assuming that is the problem do you have a solution?

[m1l]

Just had a quick look about mySQL and COUNT(distinct and got the following from the MySQL site

Stanislav Yordanov:
Counting rows from different tables in one query:

SELECT COUNT(DISTINCT table1.ID) AS table1, COUNT(DISTINCT table2.ID) AS table2, COUNT(DISTINCT table3.ID) AS table3 FROM table1, table2, table3

DISTINCT in COUNT() is supported in version 3.23.2 and upper.

[aspen]

Well thats good to know.. the last time I had to try this it wasn't supported and I do have a newer version of MySQL now... I'll give it a try.

[aspen]

That doesn't fix it... logically I don't think it would because you're not counting the number of distinct values, rather you just want the total number of values.

Though I have confirmed that the number is being created by replies * votes.

[aspen]

I tried changing the type of join I did to a natural join and it had the same result...

[aspen]

Ah ha. I get whats happening now.

When doing a left join it makes additional rows when you have a many to many relationship.

So for the joins to work since there are like 25 votes and 1 reply it must make 25 rows. Thus in my count there were 25 replies.

Using distinct was the right solution - however replacing the group by clause with the distinct didn't work - the correct answer was to add the distinct qualifier to the select statement and leave on the group by clause (since I still needed to group the results of the aggregate function).