SQL grouping query results for a single column

Question

I'm trying to complete a sql query but it gives me trouble right now, maybee someone could help me with it.

Here's the tables in shorter version (sorry for the french names):

Lexique

ID  
terme_fr  
definition_fr    

DomaineDuTerme

lexique_id  
domaine_id  

Domaine

ID  
domaine_fr

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.

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.

Here is my request so far:

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'  `

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.

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.

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.

Many Thanks !

Answer 1

If I'm understanding your question correctly, you want to combine all the domaine_fr rows into a single row, perhaps comma delimited? If so, the correct answer depends on the RDBMS that you are using. For MySQL, you could use GROUP_CONCAT and for Oracle you could use LISTAGG. SQL Server makes it a little more difficult, but you could use FOR XML to achieve the same results.

---

MySQL:

SELECT   
  Lex.terme_fr,   
  Lex.definition_fr,   
  GROUP_CONCAT(Dom.domaine_fr SEPARATOR ', ') AS 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'  `
GROUP BY  
  Lex.terme_fr,   
  Lex.definition_fr

Oracle:

SELECT   
  Lex.terme_fr,   
  Lex.definition_fr,   
  LISTAGG(Dom.domaine_fr, ',') WITHIN GROUP (ORDER BY Dom.domaine_fr) AS 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'  `
GROUP BY  
  Lex.terme_fr,   
  Lex.definition_fr

SQL Server:

SELECT   
  Lex.terme_fr,   
  Lex.definition_fr,   
  STUFF(( SELECT  ', ' + Dom2.domaine_fr
            FROM    Domaine Dom2 
            WHERE   Dom.Id = Dom2.Id
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 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'  `
GROUP BY  
  Lex.terme_fr,   
  Lex.definition_fr