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: #TABLE words_moves; mid | action | gid | uid | played | tiles | score -----+--------+-----+-----+-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------- 2 | play | 1 | 1 | 2016-08-24 20:36:39.888224+02 | [{"col": 7, "row": 8, "value": 2, "letter": "Р"}, {"col": 7, "row": 7, "value": 1, "letter": "Е"}, {"col": 7, "row": 6, "value": 10, "letter": "Ф"}] | 13 3 | play | 2 | 1 | 2016-08-24 21:48:14.448361+02 | [{"col": 7, "row": 12, "value": 5, "letter": "Ь"}, {"col": 7, "row": 10, "value": 1, "letter": "Е"}, {"col": 7, "row": 9, "value": 1, "letter": "О"}, {"col": 7, "row": 11, "value": 10, "letter": "Ш"}, {"col": 7, "row": 8, "value": 2, "letter": "Р"}, {"col": 7, "row": 7, "value": 2, "letter": "П"}] | 31 4 | play | 1 | 2 | 2016-08-24 21:50:55.231266+02 | [{"col": 8, "row": 8, "value": 2, "letter": "Й"}, {"col": 8, "row": 7, "value": 1, "letter": "А"}, {"col": 8, "row": 6, "value": 2, "letter": "Р"}, {"col": 8, "row": 5, "value": 2, "letter": "С"}] | 33 (3 rows) # SELECT gid, EXTRACT(EPOCH FROM created)::int AS created, player1, COALESCE(player2, 0) AS player2, COALESCE(EXTRACT(EPOCH FROM played1)::int, 0) AS played1, COALESCE(EXTRACT(EPOCH FROM played2)::int, 0) AS played2, ARRAY_TO_STRING(hand1, '') AS hand1, ARRAY_TO_STRING(hand2, '') AS hand2, bid FROM words_games WHERE player1 = 1 OR player2 = 1; gid | created | player1 | player2 | played1 | played2 | hand1 | hand2 | bid -----+------------+---------+---------+------------+------------+---------+---------+----- 2 | 1472068074 | 1 | 0 | 1472068094 | 0 | ЫТОВЕРЛ | ЕНХЯЭАК | 1 1 | 1472063658 | 1 | 2 | 1472063800 | 1472068255 | ВГЦЕСИУ | ННДНСВТ | 1 (2 rows) Then I am trying to perform the LEFT JOIN to return active games and recent moves for player 1, but for some reason the first two columns are empty: # 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; tiles | score | gid | created | player1 | player2 | played1 | played2 | hand1 | hand2 | bid -------+-------+-----+------------+---------+---------+------------+------------+---------+---------+----- | | 2 | 1472068074 | 1 | 0 | 1472068094 | 0 | ЫТОВЕРЛ | ЕНХЯЭАК | 1 | | 1 | 1472063658 | 1 | 2 | 1472063800 | 1472068255 | ВГЦЕСИУ | ННДНСВТ | 1 (2 rows) Why aren't m.tiles and m.score returned please? Regards Alex
