I have the following scenario. A
users table, a
records table and a
A user can be associated to zero or more records either directly with the records table
records.user_id = users.id
or via a membership (the membership table contains
memberships.user_id = users.id
Let's assume I want to load all the records managed by the user. The query directly translates into
SELECT records.* FROM records WHERE records.user_id = ? UNION SELECT records.* FROM records INNER JOIN memberships ON memberships.record_id = records.id WHERE memberships.user_id = ?
Is there a way to combine these two queries into a single one without
I tried starting from:
SELECT records.* FROM records LEFT OUTER JOIN memberships ON memberships.record_id = records.id WHERE records.user_id = ? OR memberships.user_id = ?
But it also returns the records where the
JOIN matches for other user memberships. Any hint?