Triple Join query to get data from 4 tables at once

[jimmyoneshot]

I have 4 tables in my db, a users table (primary key: user_id), a roles table (primary key: role_id), a teams table (primary key: team_id) and a a users_teams table (no primary key).

 

The users_teams is a join table to indicate what user is on each team and what their role is in the team. It uses all the above stated primary keys as foreign keys.

 

At the moment I have a query which gets what users are in each team and displays these on the users own page like this:-

 

SELECT teams.* FROM teams LEFT JOIN users_teams ON (teams.team_id=users_teams.team_id) WHERE users_teams.user_id = $userid

 

I want to also be able to get ALL the info relating to each users role within the team from the roles table in the same query. How can I do this?

[gizmola]

You just add additional LEFT JOIN statements.

 

SELECT teams.*, roles.* 
FROM teams
LEFT JOIN users_teams 
ON (teams.team_id=users_teams.team_id) 
LEFT JOIN roles
ON (roles.role_id = users_teams.role_id)
WHERE users_teams.user_id = $userid

[jimmyoneshot]

That's perfect and simple. The answer to this was hard to find on the web. Thanks so much, that is easily expandable and will help me in the future.

[jimmyoneshot]

I now actually want to be able to advance this just slightly by including images representing each team. You see I have a seperate table called photos set up in the following way:-

 

PHOTOS

________________________

 

photo_id  | of_id |  photo_filename  | is_main

 

The of_id is used to indicate which team or user the image is of i.e it corresponds to either the user_id of users or the team_id of teams. The is_main field is used to determine if the image is the main 'profile' image for that user/team. 1 = true, 0 = fasle

 

How can I modify the query provided above so that the images relating to each team the user is in which are set as is_main = 1 are also included?

[jimmyoneshot]

Sorry for another post but I forgot to say I've tried the following to no avail:-

 

SELECT teams.*, roles.*, photos.* FROM teams LEFT JOIN users_teams ON (teams.team_id=users_teams.team_id) LEFT JOIN roles ON (roles.role_id = users_teams.role_id) LEFT JOIN photos ON (photos.of_id = users_teams.team_id) WHERE users_teams.user_id = $userid

[fenway]

"To no avail" -- do you still get the results you did before?