How can I join a third table into the result of two joint tables?
<p>I was writing a python program to read the names of columns and the type of the columns need to satisfied.</p>

<p>I want to place the results like this: </p>

<pre><code>id_index=Column(int)
</code></pre>

<p>by using print</p>

<pre><code>print "{0}{1}=Column({2})".format(" "*5,SysColumns.name,SysTypes.xtype)
</code></pre>

<p>I will use three tablesï¼ <code>SysColumns</code>, <code>SysObjects</code>, and <code>SysTypes</code>:</p>

<pre><code>class SysColumns(BaseModel):
     __tablename__ = "SysColumns"
     name=Column(String,primary_key=True)
     colorder=Column(Integer,primary_key=True)
     id=Column(Integer,primary_key=True)
     xtype = Column(String, primary_key=True) 

class SysObjects(BaseModel):
     __tablename__ = "SysObjects"
     name=Column(String)
     id=Column(Integer,primary_key=True)
     xtype=Column(String,primary_key=True)

class SysTypes(BaseModel):
    __tablename__ = "SysTypes"
    name = Column(String, primary_key=True)
    xtype = Column(String)
</code></pre>

<p><code>SysColumns</code> has the same column <code>id</code> in <code>SysObjects</code>, while <code>SysTypes</code> has the same  column <code>xtype</code> in <code>SysColumns</code>.</p>

<p>The original code is as below:</p>

<pre><code>def dispMod(session,tableName):
    result=session.query(SysColumns). \
        join(SysObjects,SysColumns.id==SysObjects.id).\
            filter(SysObjects.xtype=='u').\
            filter(SysObjects.name==tableName).\
            order_by(SysColumns.colorder)

    for sysColumns in result:
        print "{0}{1}=Column({2})".format(" "*5,SysColumns.name,SysColumns.xtype)
</code></pre>

<p>The result can be displayed like: </p>

<pre><code>id_index=Column(56)
</code></pre>

<p>Now I need to search the number 56 in <code>SysTypes</code> to change it in to the type it stand for.</p>

<p><code>SysTypes</code> has columns of <code>SysTypes.xtype</code> and <code>SysTypes.name</code>.
I want it to show me the name instead of the <code>xtype</code>:</p>

<pre><code>print "{0}{1}=Column({2})".format(" "*5,SysColumns.name,Systypes.name)
</code></pre>

<p>I don't know how to use the result above to join the Systypes table to find the name in it.</p>

<p>Can I write the query to search <code>SysColumns.name</code> and <code>SysTypes.name</code> for one time?</p>

<p>Simply add the join after the other and select the names only:</p>

<pre><code>session.query(SysColumns.name, SysTypes.name).\
    join(SysObjects, SysColumns.id == SysObjects.id).\
    join(SysTypes, SysTypes.xtype == SysColumns.xtype).\
    filter(SysObjects.xtype == 'u').\
    filter(SysObjects.name == tableName).\
    order_by(SysColumns.colorder)
</code></pre>

python sqlalchemy