How to dynamically join multiple tables using SQLalchemy Core?

Question

I have one parent table which holds the primary keys of several child tables. The number of child tables can be arbitrary at run time. Using SQLalchemy core, how can I join multiple child tables to this parent?

Say I have tables of class sqlalchemy.schema.Table with valid FK constraint; how do I construct this query?

I've tried i.e.;

childJoins= [sa.join(parentTable,childTables[0]),sa.join(parentTable,childTables[1])]
# childTables is a list() of Table objects who are guaranteed linked by pk 

qry = sa.select(["*"],from_obj=childJoins)

Which gives;

SELECT * 
FROM 
parentTable JOIN child1 ON child1.P_id = parentTable.C1_Id, 
parentTable JOIN child2  ON child2.P__id = parentTable.C2_Id

So parentTable is listed twice...

Tried many more variations using join() etc. looked at docs, but I still can't get what I want;

SELECT *
FROM parentTable
JOIN child1 ON parentTable.C1_Id=child1.P_Id
JOIN child2 ON parentTable.C2_Id=child2.P_Id 
...
JOIN childN ON parentTable.CN_Id=childN.P_Id

Answer 1

Simply chain the joins:

childJoins = parentTable
for child in childTables:
    childJoins = childJoins.join(child)

query = sa.select(['*'], from_obj=childJoins)