Multi Table Select Questions

aaron_karp

I'm trying to do a select query that takes all the rows in one table, Locations, and then counts the number of people at those locations. That's defined in a UserLocations table. So that should be easy enough. But, I also want to find the number of friends at the location. Friendships are defined by a userid-friendid relationship in the Friendships table.

The FriendId in Friendships, in this query, matches up with the UserId in UserLocations

Locations

LocationName
LocationId

UserLocations

LocationId
UserId (refers to users table)

Friendships

FriendId
UserId
(both refer to users table)

So far, I've got something like this. It returns the Locations very well, but it messes up the counts. Where am I going wrong?

			[CODE] $sql = "SELECT l.LocationName as LocationName, l.LocationId as LocationId,
COUNT(ul.UserId) as TotalAtLocation,
COUNT(f.FriendId) as FriendsAtLocation
FROM Locations l
LEFT JOIN UserLocations ul ON l.LocationId = ul.LocationId
LEFT JOIN Friendships f ON ul.UserId = f.FriendId  
GROUP BY l.LocationId";[/CODE]

Any thoughts? I'm lost. And I guess I don't totally understand Joins, Counts, or Group Bys either.

Thanks guys.

coreyp_1

Someone can correct me if I'm wrong, but I don't think you can have two COUNT() statements in the same query.

If you are using MySQL 4.1+, then subqueries might do what you need. Otherwise, you will probably need to use separate queries.

I make no guarantees... 🙂

$sql = "SELECT l.LocationName, l.LocationId, TotalAtLocation, FriendsAtLocation,
	FROM Locations l
	LEFT JOIN (SELECT LocationID, COUNT(ul.UserID) AS TotalAtLocation
		FROM UserLocations ul
		GROUP BY LocationID)
		USING LocationId
	LEFT JOIN (SELECT LocationID, COUNT(f.FriendID) AS FriendsAtLocation
		FROM UserLocations ul
		LEFT JOIN Friendships f
			ON ul.UserID = f.FriendID
		WHERE f.UserID=$userid
		GROUP BY LocationID)
		USING LocationID";

Even if it doesn't work (I couldn't test it, after all!), it should start you in the right direction.

Note the variable $userid that you will need to supply.

Sxooter

Are you sure your data are what you think they are here? Have you got a short "test case" with create table, inserts, and select you could show us to explain what you want and what you're really getting? I'm not completely sure why your query's not working, but seeing the data that go into the table and such might help me understand a little better.