Easy JOIN, I just know nothing...

[aaron_karp]

Just getting started with joins. I've usually copped out in the past by writing multiple queries. Here's the deal.....

Tables
UsersBasicInfo(UserFirstName, UserLastName, UserId)
Friendships(UserId, FriendId, Nickname, IsStarred)
UserLocations(LocationId, UserId, CheckinTimestamp)
Locations(LocationName, LocationAddress, LocationId)

So I'm trying to select all of the logged in user's friends (userID stored in session variable). That's easy enough. But I also want to select their locations, regarless of whether or not they are actually listed in the UserLocations table. I'm pretty sure this requires a LEFT JOIN, si?

Here's the query I have written. Right now, it returns nothing.

				$sql = "SELECT	UsersBasicInfo.UserFirstName as Fname, UsersBasicInfo.UserLastName as Lname, UsersBasicInfo.UserId,
							   	Friendships.Nickname as Nickname, Friendships.IsStarred, 
								UserLocations.LocationId, UserLocations.CheckinTimestamp as Checkin, 
								Locations.LocationName as Location, Locations.LocationAddress as Address
						FROM   	UsersBasicInfo,	Locations,
								Friendships LEFT JOIN UserLocations ON Friendships.FriendId = UserLocations.UserId
						WHERE 	Friendships.UserId = " . $_SESSION['userID'] . " AND Friendships.FriendId = UsersBasicInfo.UserId
								AND UserLocations.LocationId = Locations.LocationId";

Can anyone help me? I've gone over a bunch of tutorials but everything I find is too simplistic and doesn't involve 4 tables.

I feel like this is a pretty simple query. Once you guys show me how to do it I can use it as an example to learn from. Thanks!

[coreyp_1]

Be careful not to specify your relationships in you WHERE clause. Try this:

$sql = "SELECT ubi.UserFirstName as Fname, ubi.UserLastName as Lname, ubi.UserId,
    f.Nickname as Nickname, f.IsStarred,
    ul.LocationId, ul.CheckinTimestamp as Checkin,
    l.LocationName as Location, l.LocationAddress as Address
  FROM Friendships f
  LEFT JOIN UsersBasicInfo ubi ON f.FriendID = ubi.UserID
  LEFT JOIN UserLocations ul ON ubi.Userid = ul.Userid
  LEFT JOIN Locations l ON ul.LocationID = l.LocationID
  WHERE f.UserID = " . $_SESSION['userID'];

[aaron_karp]

Haha what can I say, Corey, worked like a charm. Thanks. I think I can use this as an example for the future too.