Simple way to write SQL query for select all data of linked tables in PostgreSQL

Question

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"?

question from:https://stackoverflow.com/questions/65863542/simple-way-to-write-sql-query-for-select-all-data-of-linked-tables-in-postgresql

Answer 1

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}