Weird problem with select count from multiple tables (with joins)?

When you add LEFT JOIN hintout_thanks AS ht2 ON h. Ntout_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).

When you add LEFT JOIN hintout_thanks AS ht2 ON h. Ntout_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) 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.

Aha I see, is a sub select a good idea or is it better to find a way using joins or distinct, or I don't know what other methods there are...? – roy naufal May 4 at 13:17 @roy naufal, both have their place. I always go for the join + distinct route first, and if that's too slow I try a sub-select and see if that's faster. You need to time the code and test it to see which is better.

– Johan May 4 at 13:20.

SELECT h. *, h. Permalink AS hintout_permalink, 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. 0) AS commentsCount, COALESCE( ( SELECT COUNT(*) FROM hintout_comments hti WHERE hti.0) AS thanksCount FROM hintouts AS h JOIN hinter_follows AS hf ON hf. Nter_id JOIN hinters AS hi ON hi.

Nter_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. Thanker_user_id=1 WHERE hf.

User_id = 1.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.