I am using Metabase to ask questions on my DB. I am trying to join two tables where the same info (user ID) has two different names. The code I wrote is as follows:
SELECT
game_states.game_module AS game, count(*)
FROM
game_states gs LEFT JOIN users u ON gs.user_id = u.id;
WHERE
games_states.state = 'after_hands'
AND
user.last_joined_stamp > now() - interval '30 days'
GROUP BY
1
ORDER BY
2 DESC
I keep getting the following error: ERROR: invalid reference to FROM-clause entry for table "game_states" Hint: Perhaps you meant to reference the table alias "gs". Position: 127
Once you define a table alias, use it!
SELECT gs.game_module AS game, count(*)
-------^
FROM game_states gs LEFT JOIN
users u
ON gs.user_id = u.id;
WHERE gs.state = 'after_hands' AND
------^
u.last_joined_stamp > now() - interval '30 days'
------^
GROUP BY 1
ORDER BY 2 DESC;
Incidentally, you probably intend:
SELECT gs.game_module AS game, count(u.id)
FROM game_states gs LEFT JOIN
users u
ON gs.user_id = u.id AND
u.last_joined_stamp > now() - interval '30 days'
WHERE gs.state = 'after_hands'
GROUP BY 1
ORDER BY 2 DESC;
You are using LEFT JOIN, so presumably want to include all matching games -- even those with no matching users. If so, don't filter on u in the where clause and count the matches, so you can get 0.