Get data from three table SQL

2
votes

My MySQL tables structure (3 table)

users

id
username

images

id
user_id
image

user_follow

id
user_id
follow_id

I try to do query which give me all images from "images" table from me (user_id = 3) and my friends which I follow "user_follow" table

Now query and it give me all pictures from user I follow, I do not know what changes to add to return also my images (my user_id = 3). All users names must be in username (not id), username is in "users" table

 $sql = "SELECT u.username, p.image, p.date
    FROM users u, user_follow f, images p
    WHERE f.user_id = 3 AND f.follow_id = u.id AND f.follow_id = p.user_id
    ORDER BY p.date DESC";

it return: 

[0] => Array
    (
        [id] => 8
        [image] => fsfsf
        [date] => 2012-01-24 14:58:14
    )

[1] => Array
    (
        [id] => 7
        [image] => first.jpg
        [date] => 2012-01-24 14:42:27
    )

[2] => Array
    (
        [id] => 7
        [image] => second.jpg
        [date] => 2012-01-24 14:42:27
    )

[3] => Array
    (
        [id] => 6
        [image] => the_last.jpg
        [date] => 2012-01-24 01:49:45
    )

users and their images which I follow but no my images from my user_id

2
phpmysqlsql
if you are trying to get "all images from 'images' table", then where does the username variable come into play? - bowlerae
As I correct understand you here f.follow_id = u.id. My users have id in users table but others have the same id in user_id field - Viktors

2 Answers

2
votes
SELECT
   images.*,
   users.username
FROM images
LEFT JOIN users ON images.user_id = users.id
LEFT JOIN user_follow ON images.user_id = user_follow.follow_id
WHERE images.user_id = 3 OR user_follow.user_id = 3
ORDER BY images.date DESC
1
votes
SELECT image
FROM images
WHERE user_id = 3
OR user_id IN (
    SELECT follow_id
    FROM user_follow
    WHERE user_id = 3
)
ORDER BY date DESC;