I try with a CTE but cannot figure the syntax:
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=acd6d06a7ea2efc73a0771530832d77e
WITH cte AS (
SELECT uid
FROM words_social
WHERE social = in_social
AND sid = in_sid
LIMIT 1
)
SELECT
CASE WHEN c.uid = cte.uid THEN 1 ELSE 0 END,
c.msg
FROM words_chat c
JOIN words_games g USING (gid)
JOIN words_users myself ON (myself.uid IN (g.player1, g.player2)
AND myself.uid = cte.uid)
JOIN words_users opponent ON (opponent.uid IN (g.player1,
g.player2) AND myself.uid <> cte.uid)
JOIN cte
WHERE c.gid = in_gid
AND (c.uid = myself.uid OR NOT opponent.muted)
ORDER BY c.CREATED ASC;
ERROR: syntax error at or near "WHERE"
LINE 67: WHERE c.gid = in_gid
^
And if I remove the "JOIN cte" line, then the error is:
ERROR: missing FROM-clause entry for table "cte"
LINE 64: ...elf.uid IN (g.player1, g.player2) AND myself.uid = cte.uid)
^
>
