sql query is behaving strange

Question

I have a query

select c.CommentId
,c.CommentText
, c.CommenterId
, c.CommentDate
, u.first_name
, u.last_name
, i.ImageName
, i.Format 
from comment c 
join users u 
  on  c.CommenterId = u.user_id 
join user_profile_image i 
  on u.user_id = i.UserId 
where PostId = 76 
 order 
  by CommentDate desc 
limit 10

This query returns empty results when i.ImageName field is empty in the table. I want to return the row if the ImageName field is emty. How should I do this?

Out of curiosity, why would you call a field `user_id` in one table but `userid` in another? — Gordon Linoff, Nov 21 '15 at 22:31
@GordonLinoff what do you mean? I used some ready made login system that used the underscore but I personally don't like this .. If this answers your curiosity? — whatever, Nov 21 '15 at 22:33
FWIW, I prefer it with the underscore. It prevents confusion of the Pen Island variety. — Strawberry, Nov 21 '15 at 23:45
penisland - underscores would help make that less embarrassing ;) pen_island. — BenOfTheNorth, Nov 22 '15 at 19:02
@whatever . . . I don't have really strong opinions about the underscore (personally, I would leave it out). I do have strong opinions about consistency. — Gordon Linoff, Nov 23 '15 at 15:04

score 2 · Accepted Answer · edited May 23 '17 at 11:59

2

JOIN defaults to INNER JOIN for MySQL - try changing

join user_profile_image i

to

LEFT join user_profile_image i

The accepted answer here has a good visual explanation: Difference in MySQL JOIN vs LEFT JOIN

edited May 23 '17 at 11:59

Community

1
1

answered Nov 21 '15 at 22:22

BenOfTheNorth

2,904
1
20
46

Right now I am either too much tired or I am stupid.. I had no clue what to do... hhh thanks.. – whatever Nov 21 '15 at 22:25

score 2 · Answer 2 · answered Nov 21 '15 at 22:29

To include the rows when the ImageName field is empty, use LEFT JOIN, like this:

SELECT c.CommentId,c.CommentText, c.CommenterId, c.CommentDate, u.first_name,
u.last_name,i.ImageName,i.Format
FROM comment c 
INNER JOIN users u ON c.CommenterId=u.user_id 
LEFT JOIN user_profile_image i ON u.user_id=i.UserId
WHERE PostId = 76
ORDER BY CommentDate DESC
LIMIT 10;

score 2 · Answer 3 · answered Nov 21 '15 at 22:29

The issue isn't exactly that i.ImageName is empty. The issue is that there is no image associated with the user. The join doesn't find an image, and without a match, the user isn't returned.

The solution is to use left join. My inclination is to write the query entirely with left join:

select c.CommentId, c.CommentText, c.CommenterId, c.CommentDate,
       u.first_name, u.last_name,
       i.ImageName, i.Format
from comment c left join
     users u
     on c.CommenterId = u.user_id left join
     user_profile_image i
     on u.user_id = i.UserId
where PostId = 76
order by c.CommentDate desc
limit 10;

Note: This assumes that PostId is in the comment table, which seems reasonable given the table names.

sql query is behaving strange

3 Answers3