Query help in forumdisplay.php

[Antivirus]

I have a mod I've written that modifies the threads shown in forumdisplay for a particular forumid. My problem however isn't with the threads which are queried, but the problem lies with navigating multiple pages.

Incorrect values are passed to the $limitlower, $limitupper, and $totalthreads vars, which basically either cause the pagenav to no longer show, or give the incorrect values to the "showing threads 1 to 1 of 1" phrase.

I have narrowed it down to the threadscount query which is run before the threadids query and the main query. Basically, the unmodified threadscount query looks something like this:

PHP Code:

SELECT COUNT(*) AS threads, 1 AS newthread
    $hook_query_fields
FROM thread AS thread
    $hook_query_joins
WHERE forumid = 34
    AND sticky = 0
    $hook_query_where 


the result of this query is shown in the attached image 'query1.jpg'


After my plugin code inserted at hook: 'forumdisplay_query_threadscount' the query looks like this:

PHP Code:

SELECT COUNT(*) AS threads, 1 AS newthread
FROM thread AS thread
    LEFT JOIN post AS p ON (p.postid = thread.firstpostid)
    LEFT JOIN sc_postgroup AS sc_pg ON (sc_pg.postid = p.postid)
    LEFT JOIN socialgroup AS sg ON (sg.groupid = sc_pg.groupid)
    LEFT JOIN sc_status AS sc_status ON (sc_status.statusid = p.sc_statusid)
WHERE forumid = 34
    AND sticky = 0 


the result of the modified query is shown in the attached image 'query2.jpg'

As you can see, the end result is now grouping the incorrect way - as there are 16 results counted (as opposed to 10 as there should be).

I am pretty sure it's due to the way COUNT() is interacting with the type of JOINS I am using in the modified query, however I am not too familiar with the logic of RIGHT, INNER, OUTER, if this is indeed the problem, etc...

Any ideas here? I have been trying different types of joins, however i might be missing the correct combination.

[Antivirus]

ok - i have definitely narrowed it down to somethign to do with this JOIN in particular:

LEFT JOIN sc_postgroup AS sc_pg ON (sc_pg.postid = p.postid)


There are sometime 0 or more records in sc_postgroup with the same postid. I need this here though because users are only able to see threads that are associated with the socialgroups they have joined. Maybe this JOiN should be replaced with a subquery?

--------------- Added [DATE]1249100198[/DATE] at [TIME]1249100198[/TIME] ---------------

*solved, please disregard*