3 ms·
This assumes a single foreign reference What if you have two? I would suggest this syntax: SELECT * FROM foo JOIN bar VIA foo.bar_id LEFT JOIN baz VIA
by continuational 5y ago
This assumes a single foreign reference What if you have two? I would suggest this syntax:
SELECT *
FROM foo
JOIN bar VIA foo.bar_id
LEFT JOIN baz VIA bar.baz_id
- JoelJacobson 5y agoYour VIA suggestion seems to use the column name(s). The problem with that is when you have multiple foreign keys on the same column, or more likely, a foreign key on multiple columns. My suggestion instead uses the constraint name of the foreign keys, which is defined using "CONSTRAINT constraint_name FOREIGN KEY ...", which avoids these two problems. Some examples: CREATE TABLE users ( id bigint NOT NULL GENERATED ALWAYS AS IDENTITY, parent_user_id bigint, PRIMARY KEY (id), CONSTRAINT parent FOREIGN KEY (parent_user_id) REFERENCES users ); SELECT parent.id FROM users JOIN users->parent WHERE users.id = 123 CREATE TABLE a ( id1 integer NOT NULL, id2 integer NOT NULL, PRIMARY KEY (id1,id2) ); CREATE TABLE b ( id integer NOT NULL, a_id1 integer, a_id2 integer, PRIMARY KEY (id), CONSTRAINT a FOREIGN KEY (a_id1,a_id2) REFERENCES a ); SELECT a.id1, a.id2 FROM b JOIN b->a WHERE b.id = 123;
- continuational 5y agoAh! Clever. I somehow missed that it was the constraint name.