Paul Paul - 12 days ago 9
SQL Question

Using an else in an SQL query

I have the following query in which joins a couple of tables. One of those tables is

profile_img
. This table houses profile images for users who have uploaded them, otherwise they are given
profile_images/default.jpg
and the user is not in the
profile_img
table, so I need a case of sorts to check if they aren't in that table and then to set their profile image as the default.

Here is my query.

SELECT f.*, u.*, p.*
FROM friends f
LEFT JOIN
profile_img p
ON p.user_id = f.friend_one
JOIN
users u
ON u.id = f.friend_one
WHERE f.friend_two = ? AND f.status = ? AND
p.id = (select max(p2.id) from profile_img p2 where p2.user_id = p.user_id)


I have tried
,coalesce(p.img, 'profile_images/default.jpg') as img
to try and get the result, but it did not help.

In another query:

(case when p.img <> '' then p.img
else 'profile_images/default.jpg'
end) as img


I am just not sure how to get it to work in this case and it does not work for this.

Answer

@marekful is correct in the use of ifnull() but there is one other fix thats needed.

In your where clause you are referencing p.id =. Since p will be null when there are no profile_img rows for that user, the whole row will be excluded from the results due to the compare to null.

This query takes into account the null p.id fields and includes them anyway by allowing p.id when its null. Now p.image_url will be null and the ifnull() method will respond with 'default.png' for those rows.

SELECT f.*, u.*, p.*, IFNULL(p.image_url, 'default.png') AS profile_pic
FROM friends f
LEFT JOIN
    profile_img p
    ON p.user_id = f.friend_one
JOIN
    users u
    ON u.id = f.friend_one
WHERE 
    f.friend_two = ? 
    AND f.status = ? 
    AND ( p.id is null or p.id = (select max(p2.id) from profile_img p2 where p2.user_id = p.user_id) )

You may also be able to do this without the OR clause by adding the subquery to the ON predicate. The subquery is then resolved before the join.

SELECT f.*, u.*, p.*, IFNULL(p.image_url, 'default.png') AS profile_pic
FROM friends f
JOIN
    users u
    ON u.id = f.friend_one
LEFT JOIN
    profile_img p
    ON p.user_id = f.friend_one and p.id = (select max(p2.id) from profile_img p2 where p2.user_id = p.user_id)     
WHERE 
    f.friend_two = ? 
    AND f.status = ? 

I'm not sure which one has better performance. You'll have to try that out for yourself.