Ok, this shoudn't be that hard at all, yet I cannot seem to find it myselve. As I'm sure someone here will see this in 5s, I'm asking for that person's help .
I have a POST and COMMENTING table (never mind the COMMENTING table name, it's cause users can comment on posts and users). Now, what I want to do is list all my posts and show how many comments were made on each of these posts. For that I've constructed the following query:
'SELECT POST.ID,Subject,Body,Name,GivenName,Surname,UpdatedAt,AuthID,
COUNT(COMMENTING.ID) AS NrOfComments FROM POST
INNER JOIN USER ON POST.AuthID = USER.ID INNER JOIN FAMILY ON USER.FamilyID = FAMILY.ID
INNER JOIN CATEGORY ON POST.CatID = CATEGORY.ID
LEFT JOIN COMMENTING ON COMMENTING.CommentableID = POST.ID WHERE CommentableType=? GROUP BY POST.ID'
The problem is, when there are posts with no commentings (a.k.a comments) they are not returned by this query. The COMMENTING table has an ID,CommentableType (either' post' or 'user' as string values) and a CommentableID (the ID of the user or post).
Can anyone spot my error? Any help greatly appreciated!
Thanks in advance,
Anzeo