MySQL - Counts and Maximums

[BillGeek]

Hi All

I'm struggling to understand the concepts of Counts and Maximums in MySQL. (or perhaps... my SQL skills...) I have the following SQL that works quite well:

SELECT
	T1.ROW_ID,
	T1.NAME AS THREAD,
	T2.NAME AS USER,
	T1.RATING,
	MAX(T3.CREATED_DATE) AS POSTCREATEDATE,
	T4.NAME AS LASTUSER,
	COUNT(T3.PAR_THREAD_ID) AS POSTCOUNT
FROM
	BGF_THREADS T1
	INNER JOIN BGF_USERS T2 ON T2.ROW_ID = T1.CREATED_BY
	INNER JOIN BGF_POSTS T3 ON T3.PAR_THREAD_ID = T1.ROW_ID
	INNER JOIN BGF_USERS T4 ON T4.ROW_ID = T3.CREATED_BY
WHERE
	T1.PAR_FORUM_ID = '1' -- Parsed in with php
GROUP BY
	T1.ROW_ID, T1.NAME, T2.NAME, T1.RATING
ORDER BY
	POSTCREATEDATE DESC

The above SQL shows all threads for a specific forum, the post count as well as the last post's title, user, and time created. (You can see it in action here)

Now I want to implement the same kind of action in the main forum page, though I can't seem to get it going...

SELECT
	T1.ROW_ID,
	T2.NAME AS TYPE,
	T1.NAME AS FORUM,
	T1.DESC_TEXT,
	COUNT( T3.PAR_FORUM_ID ) AS THREADCOUNT,
	COUNT( T4.PAR_THREAD_ID ) AS POSTCOUNT,
	MAX( T4.CREATED_DATE ) AS LAST_POST
FROM
	BGF_FORUMS T1
	LEFT OUTER JOIN BGF_FORUMTYPE T2 ON T2.ROW_ID = T1.PAR_FORUMTYPE_ID
	LEFT OUTER JOIN BGF_THREADS T3 ON T3.PAR_FORUM_ID = T1.ROW_ID
	LEFT OUTER JOIN BGF_POSTS T4 ON T4.PAR_THREAD_ID = T3.ROW_ID
GROUP BY
	T2.NAME, T1.NAME
ORDER BY
	T2.NAME, T1.NAME

This SQL gives me incorrect results. Somehow, it returns both the post count and the thread count exactly the same. (See the results here. There are no threads and posts on any forums except the first one. There should be two threads, four posts)

Can anyone explain why I am getting the incorrect thread count with the above SQL? I've been trying for hours now, but just can't get it working.

Any help would be greatly appreciated!

[si_the_geek]

There are two things that stand out to me, only one of which might have an impact on behaviour.

The first is that in both queries you are missing fields from the Group By.. which in most database systems is not possible (it gives you an error), and even when it is possible I don't know how it would behave - that may well be the cause of the issue.

The other thing is the abbreviations you use for table aliases (T1 etc), which make it very confusing to read your queries, especially when more than one query is involved. In the first query, rather than having "BGF_USERS" aliased as "T2", I would use something like "ThreadStarter"; it takes longer to write, but means that you can actually read/edit the query much more easily (note that it would be fine to have BGF_FORUMS/BGF_FORUMTYPE/BGF_THREADS as F/FType/T).

Oh, and I notice that on your page you show "Replies", when it should actually be "Posts" (as Replies = Posts - 1).

[BillGeek]

Thanks for the help, si.

I'll have a look at the grouping. I never suspected that the grouping was incorrect, but will fix it and see how it comes out. The table aliases are actually a bad habit that I picked up from analyzing SQL logs at work. I will amend shortly... And thanks for the tip about the actual site. What I'll do is change the formula to show Posts - 1.