This content has been marked as final. Show 10 replies
Are you sure your query even ran? Since you are selecting many fields plus a sum, and grouping only on one field, it would crash if I tried to run it.
The brackets in your from clause may be contributing to your problems as well.
yes the query runs fine....
i never knew brackets in the from clause where problematic.....
i mean, the query runs without the brackets, but i am still having the same issues..cheers
> i never knew brackets in the from clause where problematic.....
Using parenthesis ( ) are not problematic. But they can effect the order by which the db evaluates your JOINs, which in turn may effect the query results.
> SELECT name_usr, date_msg, content_msg, id_usr, id_msg, hot_msg, not_msg,
> username_msg, photo_usr, idmsg_msg, message_com, username_com, date_com,
> id_com, COUNT(message_com) as comma
Think about what you're actually selecting. The query is selecting a large number of detailed fields, then saying COUNT the number of message_com values for each of those each combinations. Its not surprising the count is always 1.
thanks for response,
how would you suggest i handle this?simplify the query?
There are several ways you could approach it, but the basic idea is the same. If you want a COUNT of the number of comments per MessageID, then GROUP BY the MessageID and COUNT the number of comment id's.
psuedo - code
COUNT(YourCommentsTable.CommentID) as NumberOfComments
YourMessageTable LEFT JOIN YourCommentsTable
ON YourMessageTable.MessageID = YourCommentsTable.MessageID
GROUP BY YourMessageTable.MessageID
> GROUP BY id_com
Most db's I've used wouldn't allow that query either. Usually all columns in the SELECT list, that are not included in the aggregate function, must be included in the GROUP BY
....rest of columns
ok, will give it a try, not sure im gonna be able to achieve what im after here tho..
It can be done. Whether you choose to use a separate query, QoQ, subquery or whatever depends on how you structure it. The point is you cannot get the correct counts if you're grouping by the wrong fields or too many fields. Make sense?
Yes it does make sense......the thing which i think is complicating the issue is that I am grouping the output also...so its...
comments about message
so I get the correct count of "comments about message" i.e. 3 but its only displaying one "comments about message" on screen at a time...
It late here, will have a go tommorrow and see how it goes
thanks for you help