get website followers using sql

Question

I have got 3 tables :

users

id
username

images

id
user_id
image

user_follow

id
user_id
follow_id

I am trying to get all users followers (friends) with their "username" from table users and "images" from table "images"

my query is :

"SELECT
u.username,
i.image
FROM users u, user_follow f, images i
WHERE f.user_id = 3 AND f.follow_id = u.id AND f.follow_id = i.user_id";

This query return followers only if they have image, if don't then return nothing. I try to do query to return all followers and if is image with such (images.user_id = user_follow.follow_id) then return image if users do not have image in images table then return empty array

is it possible to get

[0] => Array
(
    [username] => 6666
    [image] => 
)
[1] => Array
(
    [username] => 3333
    [image] => my.jpg
)
[2] => Array
(
    [username] => 2222
    [image] =>
) 

Answer 1

You are going to need to employ a left outer join to handle the 'even if image is null' condition. Something like the following should help:

SELECT
    u.username
  , i.image
FROM
  users u
  -- normal join of users and user_follow
  INNER JOIN user_follow f on (u.id = f.follow_id)
  -- include images if they exist, null otherwise
  LEFT OUTER JOIN images i on (f.follow_id = i.user_id)
WHERE
  f.user_id = 3
;