Re: Forward declaration of table

On 08/24/2016 01:27 PM, Alexander Farber wrote:
> Hello again,
>
> I have went the ALTER TABLE route to add my 2 "cyclic" FKs:
>
> https://gist.github.com/afarber/c40b9fc5447335db7d24
>
> And now I have these 2 tables in my 9.5.3 database:


>
> Why aren't m.tiles and m.score returned please?

Reformatted your LEFT JOIN query(courtesy of http://sqlformat.darold.net/) :

SELECT
     m.tiles,
     m.score,
     g.gid,
     extract (
         EPOCH
         FROM
         g.created ) ::INT AS created,
     g.player1,
     COALESCE (
         g.player2,
         0 ) AS player2,
     COALESCE (
         extract (
             EPOCH
             FROM
             g.played1 ) ::INT,
         0 ) AS played1,
     COALESCE (
         extract (
             EPOCH
             FROM
             g.played2 ) ::INT,
         0 ) AS played2,
     array_to_string (
         g.hand1,
         '' ) AS hand1,
     array_to_string (
         g.hand2,
         '' ) AS hand2,
     g.bid
FROM
     words_games g
     LEFT JOIN words_moves m ON (
         g.mid1 = m.mid
         OR g.mid2 = m.mid )
WHERE
     g.player1 = 1
     OR g.player2 = 1;

Looking at your tables I would start with something like:

SELECT
      wm.tiles, wm.score
FROM
    word_games AS wg
JOIN
    word_moves AS wm
ON
    wg.gid = wm.gid
WHERE
    (wg.player1 = 1
    OR
    wg.player2 = 1)

>
> Regards
> Alex


--
Adrian Klaver
adrian.klaver@aklaver.com