gistfile1.txt

groups:

| id | data |
| 1  | foo  |
| 2  | bar  |
| 3  | baz  |

memberships:

| id | group_id | user_id |
| 1  | 1        | 100     |
| 2  | 1        | 101     |
| 3  | 1        | 102     |
| 4  | 2        | 100     |
| 5  | 2        | 101     |
| 6  | 2        | 102     |
| 7  | 3        | 100     |
| 8  | 3        | 101     |
| 9  | 3        | 102     |

I want to select all groups and grab all memberships for a given user (/if they exist/)

so for user 100:

THIS WORKS:

SELECT * FROM groups LEFT OUTER JOIN memberships ON memberships.group_id = groups.id WHERE (memberships.user_id = 100 OR memberships.user_id IS NULL)

| group_id | data | membership_id | group_id | user_id |
| 1        | foo  | 1             | 1        | 100     |
| 2        | bar  | 4             | 2        | 100     |
| 3        | baz  | 7             | 3        | 100     |


BUT THIS DOESN'T:

SELECT * FROM groups LEFT OUTER JOIN memberships ON memberships.group_id = groups.id WHERE (memberships.user_id = 200 OR memberships.user_id IS NULL)

because no memberships exist for user_id 200

=> I want to grab all groups, no matter what, and memberships for a given user /if they exist/...


without where clause...

| id | data | id | group_id | user_id |
| 1  | foo  | 1  | 1        | 100     |
| 1  | foo  | 2  | 1        | 101     |
| 1  | foo  | 3  | 1        | 102     |
| 2  | bar  | 4  | 2        | 100     |
| 2  | bar  | 5  | 2        | 101     |
| 2  | bar  | 6  | 2        | 102     |
| 3  | baz  | 7  | 3        | 100     |
| 3  | baz  | 8  | 3        | 101     |
| 3  | baz  | 9  | 3        | 102     |


NULL fillers?
| id | data | id | group_id | user_id |
| 1  | foo  | -- | -------- | ------- |
| 1  | foo  | 1  | 1        | 100     |
| 1  | foo  | 2  | 1        | 101     |
| 1  | foo  | 3  | 1        | 102     |
| 2  | foo  | -- | -------- | ------- |
| 2  | bar  | 4  | 2        | 100     |
| 2  | bar  | 5  | 2        | 101     |
| 2  | bar  | 6  | 2        | 102     |
| 3  | foo  | -- | -------- | ------- |
| 3  | baz  | 7  | 3        | 100     |
| 3  | baz  | 8  | 3        | 101     |
| 3  | baz  | 9  | 3        | 102     |