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(dot)klaver(at)aklaver(dot)com