SQL Joins to get User's Friends Photos

[jimmyoneshot]

In my db I have the following tables/fields:-

 

users

user_id, name, surname

 

(This stores the user ids and their name and surname)

 

relationships

user_id, about_id, relationship

 

(This is a self join table attached to the users table showing relationships between users. A relationship of 2 means the users are friends)

 

photos

photo_id, user_id, photo_extension

 

(This contains all the photos. In this table user_id is used to determine what user uploaded the photo)

 

users_photos

user_id, photo_id, is_main

 

(This detemines what users are in/tagged in photos. The is_main field is used to determine if the photo has been set as a main profile picture for a user)

 

 

What I want now though is to be able to create a friends list for a logged in user where in each friend item the following is shown:-

 

- the profile picture of the friend i.e. is_main = 1 in the users_photos table

- each friends name and surname

- I also need the user id of each of the friends too which won't be shown but still needs to be retrieved

 

How do I achieve this using sql and joins?

 

So far I have the following, if we are to use a logged in user_id of 1 for example:-

 

SELECT users.user_id, users.name, users.surname, photos.photo_id, photos.photo_extension 
FROM users 
LEFT JOIN relationships 
ON (users.user_id=relationships.about_id) 
LEFT JOIN users_photos 
ON ( users_photos.user_id = relationships.about_id ) 
LEFT JOIN photos 
ON ( photos.photo_id = users_photos.photo_id ) 
WHERE relationships.user_id = 1 AND relationship = 2 

 

However using the above returns the photo_id and photo_extension as NULL

 

Can anyone please help me out in fixing this.

 

[Muddy_Funster]

If you are only looking to return a single data row per contdition, you should be using INNER JOIN.  Could you show some sample data from each table for us please?