CODEWITHSUNDEEP
mysqlsqlgroup-concat
cagin
cagin

Reputation: 5930

MySql:Group_Concat doesn't make group

SELECT 
    D.Name,
    GROUP_CONCAT((SELECT 
                C.Name
            FROM
                County AS C
                    INNER JOIN
                DealerCounty AS DC ON DC.CountyId = C.CountyId
                WHERE DC.DealerId = D.DealerId))
FROM
    Dealer AS D

As you can see my query I want to group county names by dealers. It returns, Error Code: 1242. Subquery returns more than 1 row How should I change my query?

Upvotes: 0

Views: 59

Answers (4)

Sebas
Sebas

Reputation: 21542

Some fine tuning along with the correction:

SELECT 
    D.Name,
    GROUP_CONCAT(c.name)
FROM
    Dealer AS D
        JOIN County AS C ON DC.DealerId = D.DealerId
        JOIN DealerCounty AS DC ON DC.CountyId = C.CountyId
GROUP BY 
    D.name

Upvotes: 2

Fabio
Fabio

Reputation: 23510

Try to add aggregate function GROUP BY

SELECT D.Name,
GROUP_CONCAT((SELECT C.Name
        FROM County AS C 
        INNER JOIN DealerCounty AS DC 
        ON DC.CountyId = C.CountyId
            ))
FROM Dealer AS D
GROUP BY D.Name

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271013

You are missing the group by statement:

SELECT  D.Name,
        GROUP_CONCAT((SELECT C.Name
                      FROM County AS C INNER JOIN
                           DealerCounty AS DC
                     ON DC.CountyId = C.CountyId))
FROM Dealer AS D
group by Name;

This version of the query, though, is much simpler:

select D.Name,
       group_concat(C.Name)
from Dealer D join
     County C
     on D.CountyId = C.CountryId
group by D.Name

Upvotes: 1

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11609

You need to add Group by D.Name

SELECT 
    D.Name,
    GROUP_CONCAT((SELECT 
                C.Name
            FROM
                County AS C
                    INNER JOIN
                DealerCounty AS DC ON DC.CountyId = C.CountyId
                WHERE DC.DealerId = D.DealerId))
FROM
Dealer AS D
group by D.Name

Upvotes: 0

Related Questions