CODEWITHSUNDEEP
mysqlgroup-byinner-joindistinctgroup-concat
Patricio Carvajal H.
Patricio Carvajal H.

Reputation: 361

MYSQL Concat several rows and join several tables

I have the following table structures:

 tMaster
 City   ClientId    ProductId   
 -------------------------------------
 1          1       1
 1          1       N
 2          1       1   
 2          2       3
 N          N       N

 tCity
 CityId   CityName      
 ----------------------
 1     City1   
 2     City2   
 N     CityN  

 tClient
 ClientId   ClientName      
 ----------------------
 1          ClientName1   
 2          ClientName2   
 N          ClientNameN  

 tProduct
 ProductId  ProductName
-------------------------------------
 1          ProductName1 
 2          ProductName2    
 3          ProductName3
 N          ProductNameN

My SQL is:

 SELECT idCity, 
 GROUP_CONCAT(DISTINCT ClientId , "|" ,ProductId ORDER BY ClientId) AS StringResult
 FROM tMaster
 GROUP BY idCity
 ORDER BY idCity

So, my results are:

 idCity StringResult
 ---------------------------------------------------------
 1      ClientId1|ProductId1,ClientIdN|ProductIdN
 2      ClientId1|ProductId1,ClientId2|ProductId3

But I need to get CityName, ClientName and ProductName from tCity, tClient and tProduct

 idCity City    StringResult
 ---------------------------------------------------------
 1      City1   ClientId1|ClientName1|Product1|ProductName1,ClientN|ClientNameN|ProductIdN|ProductNameN
 2      City2   ClientId1|ClientName1|ProductId1|ProductName1,ClientId2|ClientName2|ProductId3|ProductName3

Any way to do that?

Upvotes: 0

Views: 35

Answers (1)

stevenyoung
stevenyoung

Reputation: 26

You aren't including those columns in your SELECT statement so you won't get that in your output. Try something like this:

SELECT idCity, CityName, ClientName, ProductName
GROUP_CONCAT(
    DISTINCT tMaster.ClientId , "|" , CityName, "|", ClientName, "|", 
    ProductId , "|", ProductName ORDER BY tMaster.ClientId) AS StringResult
FROM tMaster
INNER JOIN tCity ON tMaster.city = tCity.CityId
INNER JOIN tClient ON tMaster.ClientId = tClient.ClientId
INNER JOIN tProduct ON tMaster.ProductId = tProduct.ProductId

Because some of these column names are ambiguous you will need to include the table names, i.e. tMaster.ClientId.

This should work. I would need to set up a quick schema to verify.

Upvotes: 1

Related Questions