title: SQL grouping query results for a single column
body: <p>I'm trying to complete a sql query but it gives me trouble right now, maybee someone could help me with it.</p>
  
  <p>Here's the tables in shorter version (sorry for the french names):</p>
  
  <p><strong>Lexique</strong>  </p>
  
  <pre><code>ID  
  terme_fr  
  definition_fr    
  </code></pre>
  
  <p><strong>DomaineDuTerme</strong>  </p>
  
  <pre><code>lexique_id  
  domaine_id  
  </code></pre>
  
  <p><strong>Domaine</strong>  </p>
  
  <pre><code>ID  
  domaine_fr
  </code></pre>
  
  <p>So, one element in the Lexique table can have many Domaine, and one Domaine can be used for many Lexique element. The DomaineDuTerme table is there as an intermediate that contains thoses many to many relationships.</p>
  
  <p>I would like my query to regroup domaine_fr in a single row of the recordset for each distinct Lexique element. Presently, the request returns me a record for every relationship found in the intermediate table. I need this intermediate table to know in wich Domaine(s) a Lexique element is to apply filters when needed. </p>
  
  <p>Here is my request so far:  </p>
  
  <pre><code>SELECT   
    Lex.terme_fr,   
    Lex.definition_fr,   
    Dom.domaine_fr  
  FROM 
    ((Lexique AS Lex   
     LEFT JOIN   
       DomaineDuTerme AS Ddt ON Lex.ID = Ddt.lexique_id)   
     LEFT JOIN     
       Domaine AS Dom ON Ddt.domaine_id = Dom.ID)    
  WHERE Lex.terme_fr='test'  `
  </code></pre>
  
  <p>With this request, if the 'test' element have multiples Domaines associations, I will get multiples records as results. I would like to get a single record where the multiples associations would be listed in the Dom.domaine_fr field.</p>
  
  <p>Is this possible? I did try with DISTINCT and ORDER BY, as well as with every versions of JOIN but I still get all associations.</p>
  
  <p>I know I could do it in separate queries, or in code after getting the recordset, but I'm sure there is a way via SQL. I'm also open to reorganise the database schema if this can helps.</p>
  
  <p>Many Thanks !</p>