CODEWITHSUNDEEP
mysqlconcatenation
Crackeraki
Crackeraki

Reputation: 273

GROUP_CONCAT from two tables

I have this big select, and the problem is that 2 GROUP_CONCAT from different tables. If i remove description from select color show normaly and if i keep it like this color show x3 times.

SELECT
DISTINCT m.model as model
        ,m.price as price
        ,m.size as size
        ,m.colorsetIDMainBag as mainColor
    ,GROUP_CONCAT(colorset.name) AS color
    ,leather.name AS leather
        ,leather.nameUA AS leatherUA
        ,leather.nameRU AS leatherRU
    ,GROUP_CONCAT(description.descriptionUA) AS descriptionUA
    ,GROUP_CONCAT(description.descriptionRU) AS descriptionRU
    ,GROUP_CONCAT(description.descriptionEN) AS descriptionEN
FROM
        bags as m
        LEFT OUTER JOIN colorpack
        on m.bagsID=colorpack.colorpack
        LEFT OUTER JOIN color
        on colorpack.colorID=color.colorID
        LEFT OUTER JOIN colorset
        on color.colorsetID=colorset.colorsetID
        LEFT OUTER JOIN leather
        on color.leatherID=leather.leatherID

        LEFT OUTER JOIN descriptionpack
        on m.bagsID=descriptionpack.descriptionpack
        LEFT OUTER JOIN description
        on descriptionpack.descriptionID=description.descriptionID

WHERE
        colorpack.colorpack='62'
GROUP BY model

Upvotes: 0

Views: 125

Answers (1)

O. Jones
O. Jones

Reputation: 108806

Try

GROUP_CONCAT(DISTINCT colorset.name) AS color

I presume you know you're employing a nonstandard MySQL extension to GROUP BY that often yields unpredictable and strange results. If you don't know that, please read this.

http://dev.mysql.com/doc/refman/5.1/en/group-by-extensions.html

Upvotes: 4

Related Questions