title: SQLAlchemy: Multiple tables and joins, duplicate rows
body: <p>[I feel that this is maybe / for sure (?) a duplicate, however, I've searched all day long to find a solution for this and it seems I can't get it working the way I would like to by myself.]</p>
  
  <p>In MySQL, I've got three tables, named ecordov (A), ecordovadr (B) and ecrgposvk (C).
  I've got one key linking all these; in (A) there is one row per key, in (B) and (C) there might be multiple rows per key, so, without being an expert in these questions, I think these are one-to-many relations.</p>
  
  <p>I've read the SQLAlchemy docs and set up my tables like this:</p>
  
  <pre><code>class Ecordov(Base):
  
      __tablename__ = 'ecordov'
  
      oovkey = Column(BIGINT, primary_key=True)
      oovorder = Column(BIGINT)
      ecordovadr = relationship('Ecordovadr')
      ecrgposvk = relationship('Ecrgposvk')
  
  
  class Ecordovadr(Base):
  
      __tablename__ = 'ecordovadr'
  
      ooakey = Column(BIGINT, primary_key=True)
      ooaname1 = Column(VARCHAR)
      ooaorder = Column(BIGINT, ForeignKey('ecordov.oovorder'))
  
  
  class Ecrgposvk(Base):
  
     __tablename__ = 'ecrgposvk'
  
     rgkey = Column(BIGINT, primary_key=True)
     rgposvalue = Column(DOUBLE)
     rgposordnum = Column(BIGINT, ForeignKey('ecordov.oovorder'))
  </code></pre>
  
  <p>[So, as you see, the <code>ForeignKeys</code> aren't the <code>primary_key(s)</code>, not really sure if this is a problem? However, I can't change the structure of the database.]</p>
  
  <p>My sample query looks like:</p>
  
  <pre><code>jobs = session.query(Ecordov, func.group_concat(Ecordovadr.ooaname1.op('ORDER BY')(text('ecordovadr.ooatype, ecordovadr.ooarank separator "{}"'))).label('ooaname1')).outerjoin(Ecordovadr).filter(Ecordov.oovorder.like('75289')).group_by(Ecordov.oovorder)
  </code></pre>
  
  <p>gets evaluated to:</p>
  
  <pre><code>SELECT ecordov.oovkey AS ecordov_oovkey, ecordov.oovorder AS ecordov_oovorder, group_concat(ecordovadr.ooaname1 ORDER BY ecordovadr.ooatype, ecordovadr.ooarank separator "{}") AS ooaname1 
  FROM ecordov LEFT OUTER JOIN ecordovadr ON ecordov.oovorder = ecordovadr.ooaorder 
  WHERE ecordov.oovorder LIKE '75289'
  GROUP BY ecordov.oovorder
  </code></pre>
  
  <p>and gives me the following:</p>
  
  <pre><code>for x in jobs:
      x.ooaname1
  
  u'Sorbe priv.{}Lebensn\xe4he GmbH'
  </code></pre>
  
  <p>which is my desired outcome.</p>
  
  <p>However, after joining the second table as well, regardless if using an inner- or outerjoin, via, for example, this:</p>
  
  <pre><code>jobs = session.query(Ecordov, func.group_concat(Ecordovadr.ooaname1.op('ORDER BY')(text('ecordovadr.ooatype, ecordovadr.ooarank separator "{}"'))).label('ooaname1')).outerjoin(Ecordovadr).outerjoin(Ecrgposvk).filter(Ecordov.oovorder.like('75289')).group_by(Ecordov.oovorder)
  </code></pre>
  
  <p>which gets evaluated to:</p>
  
  <pre><code>SELECT ecordov.oovkey AS ecordov_oovkey, ecordov.oovorder AS ecordov_oovorder, group_concat(ecordovadr.ooaname1 ORDER BY ecordovadr.ooatype, ecordovadr.ooarank separator "{}") AS ooaname1 
  FROM ecordov LEFT OUTER JOIN ecordovadr ON ecordov.oovorder = ecordovadr.ooaorder LEFT OUTER JOIN ecrgposvk ON ecordov.oovorder = ecrgposvk.rgposordnum 
  WHERE ecordov.oovorder LIKE '75289'
  GROUP BY ecordov.oovorder
  </code></pre>
  
  <p>gives me:</p>
  
  <pre><code>for x in jobs:
      x.ooaname1
  
  u'Sorbe priv.{}Sorbe priv.{}Sorbe priv.{}Lebensn\xe4he GmbH{}Lebensn\xe4he GmbH{}Lebensn\xe4he GmbH'
  </code></pre>
  
  <p>So, the data is tripled now. I've read in other threads about this topic that this is to be expected, especially when using the same <code>ForeignKey</code> for multiple tables.</p>
  
  <p>But I need this data "like before", which means just one entry, instead of three. I've tried using distinct() but without success so far.</p>
  
  <p>Could someone please point me into one direction, how to fix this? </p>
  
  <p>Thanks in advance and all the best!</p>