How to join three tables with outer join and cartesian product

I have three tables in a PostgreSQL DB: Users, Activities, Venues.

Users:
- id
- name

Visits:
- id
- user_id
- location_id

Venues:
- id
- name

I want to retrieve all activities for a specific user, event if the user haven't visited a location yet. I've tried it with some joins:

SELECT venues.id as venue, COUNT(activities.id) as visits
FROM users
RIGHT OUTER JOIN activities ON users.id=activities.user_id
RIGHT OUTER JOIN venues ON activities.venue_id=venues.id
WHERE users.id=1234
GROUP BY venues.id
ORDER BY venues.id

I want the visits variable to contain 0 when the user haven't visited a location (= there is no entry in activities) and the count of activities if the user have visited the location.

But I only get the locations that the user have visited:

venue | visits
  1       3
开发者_StackOverflow  2       4
  3       22
  4       1

I thought, that the RIGHT OUTER JOIN would return all entries from the right side, but it doesn't.

---

A LEFT OUTER JOIN means that you want all of the entities from the left-hand table whether or not the JOIN condition (or predicate) is satisfied.

But this is also one of those rare cases where a Cartesian product (CROSS JOIN) is actually necessary since you want to see every venue for every user. Try using the CROSS JOIN like this:

SELECT v.id as venue, COUNT(a.id) as visits
FROM users AS u
CROSS JOIN venues as v --<-- CROSS JOIN for Cartesian product between users and venues
LEFT JOIN activities AS a ON u.id = a.user_id AND a.venue_id = v.id
WHERE u.id = 1234
GROUP BY v.id
ORDER BY v.id

---

Check this out:

http://db.apache.org/derby/docs/10.2/ref/rrefsqlj57522.html

Basic join explanations-- they can be confusing. I think you either want to switch the columns in the first join, or use left outer join instead.