CODEWITHSUNDEEP
mysqlsql
whatever
whatever

Reputation: 342

sql query is behaving strange

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?

Upvotes: 0

Views: 81

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 2

Racil Hilan
Racil Hilan

Reputation: 25341

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;

Upvotes: 2

BenOfTheNorth
BenOfTheNorth

Reputation: 2872

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

Upvotes: 2

Related Questions