Re: Foreign key joins revisited

Let's look at each row your example and see if we can work it out.

I've added the "FROM permission p" and also "AS [table alias]",

otherwise the aliases you use won't exist.

> FROM permission p

This row is obviously OK. We now have "p" in scope as an alias for "permission".

> LEFT JOIN FOREIGN KEY p->permission_role_id_fkey AS r

This row would follow the FK on "p" and join the "role" table using the "permission.role_id" column. OK.

> LEFT JOIN FOREIGN KEY tr->team_role_role_id_fkey AS tr

This is where we fail. There is no "tr" table alias yet! So we cannot follow the FK.

The reason why it doesn't work is because the FK is:

FOREIGN KEY team_role (role_id) REFERENCES role

That is, the FK is on the new table we are currently joining in.