COALESCE() in where clause causing query to return 0 results

Databases
1

I am building a user feed for members of a photography site I created and this query grabs all of the activiy in the last 24 hours so I can send emails to the users. The issue I am having is if the first LEFT OUTER JOIN has no results then the query returns no results even if the second JOIN contains results. I am pretty sure I have narrowed this down to the WHERE clause and specifically the COALESCE() portion. I thought that if this function received only one value it would use it. PLease let me know what you think or if you have another solution.

SELECT a.a_id
			, a.a_us   er_id
			, a.a_activity_type
			, a.a_activity_id
			, a.a_activity_time
			, c.ic_id 
			, c.ic_status
			, c.ic_image_id
			, c.ic_creator_id
			, c.ic_comment
			, c.ic_read_status
			, s.id 
			, s.image_id 
			, s.photographer_id
			, s.user_id
			, u.u_id
			, u.u_screen_name
			, u.u_url_screen_name
			, u.u_email
			, u.u_avatar
			, u1.u_id as currentUser
			, u1.u_email as currentEmail
			, u1.u_url_screen_name as currentScreenName
			, i.i_id
			, i.i_name
			, COALESCE(c.ic_image_id, s.image_id) as image_id
			, COALESCE(c.ic_creator_id, s.photographer_id) as other_user_id
			FROM activity AS a
				LEFT OUTER JOIN image_comments as c ON 
				a.a_activity_id = c.ic_id AND a.a_activity_type = 1
				LEFT OUTER JOIN site_likes as s ON 
				a.a_activity_id = s.id AND a.a_activity_type = 2
				LEFT JOIN users as u ON
				u.u_id = COALESCE(c.ic_creator_id, s.photographer_id)
				LEFT JOIN images as i ON
				i.i_id = COALESCE(c.ic_image_id, s.image_id)
				LEFT JOIN users as u1 ON
				u1.u_id = a.a_user_id
			WHERE a.a_activity_time >= DATE_SUB(now(), INTERVAL 24 HOUR)
			AND
			COALESCE(c.ic_creator_id, s.photographer_id) != a.a_user_id
			ORDER BY a.a_activity_time DESC
2

What happens if both ic_creator_id and photographer_id are null? I think that if you give it a third option, like 0, could help. IF the issue is the COALESCE().

If that doesn’t fix it, it’s most likely not COALESCE(). I think.

HTH,

:slight_smile:

3

Thanks WolfShade for your response. The main reason I think it is the COALESCE() is because if I remove it the query works fine. In response to your idea that they both could be NULL this can not happen. Or at the very least is not happening in this case. What this whole query is doing is figuring out if the row is a comment or a like, then joining the correct table for that type of activity. The one line in the where clause is grabbing the id of the photographer who created the art work and making sure that it isn’t the same as the person who left the comment. basically I don’t want to say to someone check it out you left a comment on your own photo.

So no matter what one of the two fields has a value.

4

you know how you have an AND condition on the activity type in each of the first two joins? you could add another AND condition on either c.ic_creator_id or s.photographer_id in the join, and do away with the COALESCE in the WHERE clause completely

5

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.