Problem with left join.

[Anzeo]

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

[Anzeo]

Does someone have a clue? Still not able to figure out what I'm doing wrong here.

 

TIA

[DavidAM]

Since you are using a condition on a column in the COMMENTING table, you are going to have to have that condition NOT exclude the POSTS with no comments:

 

'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='post' OR CommentableType IS NULL) GROUP BY POST.ID'

 

When you do an outer JOIN you get NULLs for all columns in the table (in this case COMMENTING) if there is no match; even if the columns are specified as NOT NULL, the query will return NULL.  Since you specified only count the rows with CommentableType = 'post' (why was that a ? in your code?) the POST rows without comments were not in the resultset.  By adding the OR ... IS NULL we allow these POST rows and the count will be zero.

 

 

[Anzeo]

Awesome David! This works, thanks for the clear explanation.  There's a '?' in my code as I'm using data binding in my queries.

Thanks again!