Re: Forward declaration of table

[Adrian Klaver <adrian.klaver@xxxxxxxxxxx>]

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@xxxxxxxxxxx


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general