Hi there, I am wondering how the following would be translated to sqlalchemy.
I have three tables: "ClientMachine" "MachineToken" "Token" The table MachineToken acts as n:m mapping between "ClientMachine" and "Token". Each machine can have several tokens assigned. Looks like this: machinetoken_table = sa.Table('MachineToken', meta.metadata, sa.Column('id', sa.types.Integer(), sa.Sequence('machinetoken_seq_id', optional=True), primary_key=True, nullable=False), sa.Column("token_id", sa.types.Integer(), ForeignKey('Token.privacyIDEATokenId')), sa.Column("machine_id", sa.types.Integer(), ForeignKey('ClientMachine.id')), sa.Column("application", sa.types.Unicode(64)), UniqueConstraint('token_id', 'machine_id', 'application', name='uix_1'), implicit_returning=implicit_returning, ) Now I'd like to get a list of all machines and if the machine has token- and application-information also this information. I figured out a left outer join: select cl.cm_name , mt.application , (select privacyIDEATokenSerialnumber from Token where privacyIDEATokenId = mt.token_id) as Serial FROM ClientMachine cl LEFT JOIN MachineToken mt ON cl.id = mt.machine_id This will give me one machine entry per assigned "Token" or "MachineToken.application". This is my intended result. fine. I need to translate the MachineToken.token_id to a human readable Serialnumber, this is why I have the line (select privacyIDEATokenSerialnumber from Token where privacyIDEATokenId = mt.token_id) To my understandung I would do in SQLA something like this: Session.query(ClientMachine).outerjoin(MachineToken).filter(ClienteMachine.id == MachineToken.machine_id) But how would I add the Serialnumber from the table "Token"? Thanks a lot and kind regards Cornelius -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.