On Wed, May 4, 2022 at 8:21 AM Alexander Farber <alexander.farber@xxxxxxxxx> wrote:
David, I am trying your suggestion:On Wed, May 4, 2022 at 4:27 PM David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:Assuming the base query is capable of returning all related chat messages for both users (I'd probably place that portion into a CTE) the rows you want to filter out are those whose c.uid is not my own, but only if their muted property is true. It makes it easier to understand if you join words_users twice, defining one as "them" and one as "me". Then you can say something like: WHERE (c.uid = me.uid) OR NOT(them.muted)
You missed quoting the part where I describe the on clauses you need to distinguish between "them" and "me"
Me: u.uid in (player...) and (s.uid = u.uid)
Them: u.uid in (player...) and (s.uid <> u.uid)
Them: u.uid in (player...) and (s.uid <> u.uid)
In particular, the IN _expression_ causes two rows to be returned, one for them and one for me - but for each join you only want one or the other.
David J.
