I want to return the data of all the tables linked together via foreign keys when the tables are linked together in a chain. Is there a simple SQL command for this?
SAMPLE DATA: A entrance has floor_id fk to floors table, floor table has a building_id fk to buildings, buildings has a company_id fk to companies.
Can I get all the above data given just the entrance id?
Is there a more elegant way to do this then the following SQL:
SELECT * FROM floor_entrance
LEFT JOIN floor ON floor.id = (SELECT floor_id FROM floor_entrance WHERE id = {floor_entrance_id})
LEFT JOIN building ON building.id = (SELECT building_id FROM floor WHERE id =
(SELECT floor_id FROM floor_entrance WHERE id = {floor_entrance_id}))
LEFT JOIN company ON company.id = (SELECT company_id FROM building WHERE id =
(SELECT building_id FROM floor WHERE id =
(SELECT floor_id FROM floor_entrance WHERE id = {floor_entrance_id})))
WHERE floor_entrance.id = {floor_entrance_id}
I am looking to achieve a concise way to write this postgreSQL command.
DESIRED RESULTS: "LEFT JOIN ALL ON FOREIGN KEYS ACROSS THE WHOLE SCHEMA"?
I would simply do it like this
SELECT * FROM floor_entrance A
LEFT JOIN floor B ON A.floor_id = B.id
LEFT JOIN building C ON B.building_id = C.id
LEFT JOIN company D ON C.company_id = D.id
WHERE A.id = {floor_entrance_id}