weird problem with select count from multiple tables (with joins)

I'm having an odd problem with the following query, it works all correct, the count part in it gets me the number of comments on a given 'hintout'

I'm trying to add a similar count that gets the number of 'votes' for each hintout, the below is the query:

SELECT h.*
  , h.permalink AS hintout_permalink
  , hi.hinter_name
  , hi.permalink
  , hf.user_id AS followed_hid
  , ci.city_id, ci.city_name, co.country_id, co.country_name, ht.thank_id
  , COUNT(hc.comment_id) AS commentsCount 
FROM hintouts AS h 
INNER JOIN hinter_follows AS hf ON h.hinter_id = hf.hinter_id 
INNER JOIN hinters AS hi ON h.hinter_id = hi.hi开发者_运维技巧nter_id 
LEFT JOIN cities AS ci ON h.city_id = ci.city_id 
LEFT JOIN countries as co ON h.country_id = co.country_id 
LEFT JOIN hintout_thanks AS ht ON (h.hintout_id = ht.hintout_id 
  AND ht.thanker_user_id = 1)
LEFT JOIN hintout_comments AS hc ON hc.hintout_id = h.hintout_id 
WHERE hf.user_id = 1 
GROUP BY h.hintout_id 

I tried to add the following to the select part:

COUNT(ht2.thanks_id) AS thanksCount 

and the following on the join:

LEFT JOIN hintout_thanks AS ht2 ON h.hintout_id = ht2.hintout_id

but the weird thing happening, to which I could not find any answers or solutions, is that the moment I add this addtiional part, the count for comments get ruined (I get wrong and weird numbers), and I get the same number for the thanks - I couldn't understand why or how to fix it...and I'm avoiding using nested queries

so any help or pointers would be greatly appreciated!

ps: this might have been posted twice, but I can't find the previous post

When you add

LEFT JOIN hintout_thanks AS ht2 ON h.hintout_id = ht2.hintout_id

The number of rows increases, you get duplicate rows for table hc, which get counted double in COUNT(hc.comment_id). You can replace

COUNT(hc.comment_id) <<-- counts duplicated
/*with*/ 
COUNT(DISTINCT(hc.comment_id))  <<-- only counts unique ids

To only count unique appearances on an id.

On values that are not unique, like co.county_name the count(distinct will not work because it will only list the distinct countries (if all your results are in the USA, the count will be 1).

Quassnoi
Has solved the whole count problem by putting the counts in a sub-select so that the extra rows caused by all those joins do not influence those counts.

SELECT  h.*, h.permalink AS hintout_permalink, hi.hinter_name,
        hi.permalink, hf.user_id AS followed_hid,
        ci.city_id, ci.city_name, co.country_id, co.country_name,
        ht.thank_id,
        COALESCE(
        (
        SELECT  COUNT(*)
        FROM    hintout_comments hci
        WHERE   hc.hintout_id = h.hintout_id
        ), 0) AS commentsCount,
        COALESCE(
        (
        SELECT  COUNT(*)
        FROM    hintout_comments hti
        WHERE   hti.hintout_id = h.hintout_id
        ), 0) AS thanksCount
FROM    hintouts AS h 
JOIN    hinter_follows AS hf
ON      hf.hinter_id = h.hinter_id
JOIN    hinters AS hi
ON      hi.hinter_id = h.hinter_id
LEFT JOIN
        cities AS ci
ON      ci.city_id = h.city_id
LEFT JOIN
        countries as co
ON      co.country_id = h.country_id
LEFT JOIN
        hintout_thanks AS ht
ON      ht.hintout_id = h.hintout_id
        AND ht.thanker_user_id=1 
WHERE   hf.user_id = 1