On Wed, May 4, 2022 at 9:12 AM Alexander Farber <alexander.far...@gmail.com> wrote:
> I have tried CROSS JOIN and CASE WHEN (why be greedy, right?): > > > https://dbfiddle.uk/?rdbms=postgres_14&fiddle=43a33374d15a9330145007702138822c > > WITH myself AS ( > SELECT uid > FROM words_social > WHERE social = in_social > AND sid = in_sid > LIMIT 1 > ), > opponent AS ( > SELECT CASE WHEN player1 = myself.uid THEN player2 ELSE > player1 END > FROM words_games > WHERE gid = in_gid > ) > SELECT > CASE WHEN c.uid = myself.uid THEN 1 ELSE 0 END, > c.msg > FROM myself CROSS JOIN opponent > WHERE (c.uid = myself.uid OR NOT opponent.muted) > ORDER BY c.CREATED ASC; > > but the error is: > > ERROR: missing FROM-clause entry for table "myself" > LINE 60: SELECT CASE WHEN player1 = myself.uid THEN play... > > What exactly are you trying to do in the "opponent" cte - and why do you think the myself cte is visible to it? David J.
