SQL query for finding checkins from friends
I have the following tables in a PostgreSQL:
Categories | Locations | Checkins | Users | Friendships
id | id | id | id | user_id
name | category_id | location_id | gender | friend_id
icon | name | user_id | |
Now, i want to retrieve the following information about a venue
- How many female and male users a location has
- Category name and icon
- Location name
- How many friends have checked in at a location (from a given user id)
Except the last point, I solved it. But I have troubles to count the friends from a given user id. I tried it with this query:
SELECT distinct locations.id,
max(locations.name) as name,
max(locations.location) as location,
max(categories.name) as cat,
max(categories.icon) as caticon,
SUM(CASE WHEN users.gender = 'm' THEN 1 ELSE 0 END) AS male,
SUM(CASE WHEN users.gender = 'f' THEN 1 ELSE 0 END) AS female,
SUM(CASE WHEN friendships.user_id = 1 OR friend开发者_如何学Goships.friend_id=1 THEN 1 ELSE 0 END) AS friends
FROM locations
INNER JOIN checkins ON checkins.location_id = locations.id
INNER JOIN users ON users.id = checkins.user_id
INNER JOIN categories ON categories.id = locations.category_id
LEFT JOIN friendships ON friendships.user_id = users.id OR friendships.friend_id = users.id
WHERE locations.id=7
GROUP BY locations.id
But I get a wrong number of the count for female users. Any idea what I'm doing wrong? I think I need a left join for the friendships table, because if a user has no friends (or no user is given) it should only return 0 for the friend count.
Hope I made myself clear, thx, tux
SELECT
L.id,
L.name,
c.name AS cat,
c.icon AS caticon,
COUNT(CASE u.gender WHEN 'm' THEN 1 END) AS male,
COUNT(CASE u.gender WHEN 'f' THEN 1 END) AS female,
COUNT(f.user_id) AS friends
FROM Locations L
INNER JOIN Categories c ON c.id = L.category_id
INNER JOIN Checkins ch ON ch.location_id = L.id
INNER JOIN Users u ON u.id = ch.user_id
LEFT JOIN Friendships f ON f.user_id = @user_id AND f.friend_id = ch.user_id
OR f.user_id = ch.user_id AND f.friend_id = @user_id
WHERE L.id = @location_id
GROUP BY L.id, L.name, c.name, c.icon
Drop the distinct
in the first line. You already have a group by
clause for the same field. Let me know if that helps.
精彩评论