CODEWITHSUNDEEP
phpmysqlsql
user1163798
user1163798

Reputation: 51

Return user name by user id SQL query

I have got 3 tables :

users

id
username

images

id
user_id
image

user_follow

id
user_id
follow_id

I try to make query that will show all my friends and friends of my friends. I need to get their usernames by their id (from table "users")

my query is :

$sql = "SELECT f.user_id, username, i.image, i.date FROM users u
      INNER JOIN user_follow f on (u.id = f.follow_id)
      LEFT OUTER JOIN images i on (f.follow_id = i.user_id)
      WHERE
      f.user_id = 3 OR f.user_id IN
      (
      select f2.follow_id
      from user_follow f2
      where f2.user_id = 3
      )
    ";

and itget this : 

[0] => Array
    (
        [user_id] => 6
        [username] => 7777
        [image] => 
        [date] => 
    )

[1] => Array
    (
        [user_id] => 6
        [username] => 8888
        [image] => 86o99fyyocscfvbzlco0_380.jpg
        [date] => 2012-01-23 15:03:26
    )

[2] => Array
    (
        [user_id] => 3
        [username] => 6666
        [image] => my.jpg
        [date] => 2012-01-23 16:40:28
    )

[user_id] => 6 = name in "users" table is 6666 [user_id] => 7 = name in "users" table is 7777

This query show everything good except user_id name. I need to do changes in query that it also will show user_id (from "user_follow" table) -> username (which is in "users table")

Upvotes: 2

Views: 7429

Answers (1)

Michael Krelin - hacker
Michael Krelin - hacker

Reputation: 143071

add another join with users using user_id and show username from matching row?

UPDATE:

SELECT f.user_id, fn.username, i.image, i.date
FROM
  users u
  INNER JOIN user_follow f on (u.id = f.follow_id)
  LEFT OUTER JOIN images i on (f.follow_id = i.user_id)
  JOIN users AS fn ON (f.user_id=fn.id)
WHERE
  f.user_id = 3 OR f.user_id IN
  (
  select f2.follow_id
  from user_follow f2
  where f2.user_id = 3
  )

Upvotes: 1

Related Questions