CODEWITHSUNDEEP
mysql
Abdul Manaf
Abdul Manaf

Reputation: 4888

Query returning wrong result

SELECT 
    A.id AS id, 
    Group_Concat(B.value) AS values, 
    Group_Concat(C.ac_id) AS ac_id, 
    Concat(D.first_name,' ',D.last_name) AS updatedBy
FROM A 
LEFT JOIN B 
    ON A.id = B.id 
LEFT JOIN C 
    ON A.id = C.id 
LEFT JOIN D 
    ON A.modified_by = D.user_id 
WHERE 
    A.status!='deleted' 
    AND A.created_by = '18'  
GROUP BY A.id;

Table B & C has multiple rows corressponding to A.id thats why i am using group_concat on those fields but i am getting wrong result for those grouped columns.query return result like 

+---------+-------------------------+-------------------------+-------------+
| id      | values                  | ac_id                   | updatedBy   |
+---------+-------------------------+-------------------------+-------------+
|       8 | A,A,A,B,B,B,C,C,C,D,D,D | 1,5,6,1,5,6,1,5,6,1,5,6 | Abdul       |
+---------+-------------------------+-------------------------+-------------+

but i need output like this

+---------+-------------------------+-------------------------+-------------+
| id      | values                  | ac_id                   | updatedBy   |
+---------+-------------------------+-------------------------+-------------+
|       8 | A,B,C,D                 | 1,5,6                   | Abdul       |
+---------+-------------------------+-------------------------+-------------+

Upvotes: 1

Views: 112

Answers (2)

Lightness Races in Orbit
Lightness Races in Orbit

Reputation: 385104

This problem has been solved already!

Straight out of the documentation for GROUP_CONCAT:

To eliminate duplicate values, use the DISTINCT clause.

There's even a handy example of its use.

Please use the documentation.

Upvotes: 3

fancyPants
fancyPants

Reputation: 51868

Use DISTINCT in your GROUP_CONCAT(). This way duplicates don't get displayed.

SELECT 
A.id AS id, 
Group_Concat(DISTINCT B.value) AS values, 
Group_Concat(DISTINCT  C.ac_id) AS ac_id, 
Concat(D.first_name,' ',D.last_name) AS updatedBy
FROM A 
LEFT JOIN B ON A.id = B.id 
LEFT JOIN C ON A.id = C.id 
LEFT JOIN D ON A.modified_by = D.user_id 
WHERE A.status!='deleted' AND A.created_by = '18'  GROUP BY A.id;

Upvotes: 3

Related Questions