GROUP_CONCAT with join resulting in double records

Question

I'm using the query:

SELECT  `customers`.`customers_id`, 
            `customer_name`,
            GROUP_CONCAT(customer_tags.customer_tag_name ORDER BY customer_tag_name SEPARATOR ', '),
            ''
FROM `customers`
LEFT OUTER JOIN `customer_tags_ids` ON `customer`.`customers_id` = `customer_tags_ids`.`customers_id`
LEFT OUTER JOIN `customer_tags` ON `customer_tags_ids`.`customer_tags_ids_id` = `customer_tags`.`customer_tags_id`
WHERE `customers`.`account_id` = 1
GROUP BY `customers`.`customers_id`
ORDER BY `customers`.`customers_id` desc, `customers`.`customer_name` asc

On the following tables

customers

+-------------+---------------+
|customers_id |  customer_name|
+-------------+---------------+
|1            | Customer 1    |
+-------------+---------------+
|2            | Customer 2    | 
+-------------+---------------+

customer_persons

+-----------+-------------+------------+------------+
| persons_id| customers_id| firstname  | lastname   |
+-----------+-------------+------------+------------+
|1          |  1          | Mehmet     | Yaman      |
+-----------+-------------+------------+------------+
|2          |  1          | Zafer      | Zorlu      |   
+-----------+-------------+------------+------------+
|3          |  2          | Serkan     | Eryaman    |
+-----------+-------------+------------+------------+
|4          |  2          | Nedim      | Yaman      |
+-----------+-------------+------------+------------+

customer_tags

+-------------------+--------------------+
|customer_tags_id   |  customer_tag_name |
+-------------------+--------------------+
|1                  |  Google            |
+-------------------+--------------------+
|2                  |  Yahoo             |   
+-------------------+--------------------+
|3                  |  Aol               |
+-------------------+--------------------+
|4                  |  Facebook          |
+-------------------+--------------------+

customer_tags_ids

+--------------------+------------------+--------------+
|customer_tags_ids_id|  customer_tags_id| customers_id |
+--------------------+------------------+--------------+
|1                   |  1               | 1            |
+--------------------+------------------+--------------+
|2                   |  1               | 2            |   
+--------------------+------------------+--------------+
|3                   |  2               | 1            |
+--------------------+------------------+--------------+
|4                   |  2               | 2            |
+--------------------+------------------+--------------+
|5                   |  3               | 2            |
+--------------------+------------------+--------------+
|6                   |  4               | 2            |
+--------------------+------------------+--------------+

I need to get the result:

+-----------+--------------+-----------------------------+----------------+
|customer_id|customer_name |firstname + lastname         | customer_tags  |   
+-----------+--------------+-----------------------------+----------------+
|1          | Customer 1   | Mehmet Yaman, Zafer Zorlu   | Google, Yahoo  |              
+-----------+--------------+-----------------------------+----------------+
|2          | Customer 2   | Serkan Eryaman, Nedim Yaman | Google, Yahoo, |
|           |              |                             | Aol, Facebook  |
+-----------+--------------+-----------------------------+----------------+

But when I use the query above I get the following:

| CUSTOMERS_ID | CUSTOMER_NAME |                                                                                                       CUSTOMERNAME |                                               CUSTOMERTAGS |
|--------------|---------------|--------------------------------------------------------------------------------------------------------------------|------------------------------------------------------------|
|            1 |    Customer 1 |                                                               Mehmet Yaman, Zafer Zorlu, Mehmet Yaman, Zafer Zorlu |                               Google, Google, Yahoo, Yahoo |
|            2 |    Customer 2 | Serkan Eryaman, Serkan Eryaman, Nedim Yaman, Nedim Yaman, Serkan Eryaman, Serkan Eryaman, Nedim Yaman, Nedim Yaman | Aol, Aol, Facebook, Facebook, Google, Google, Yahoo, Yahoo |

As you can see the Customer Names and the Tags are duplicated. How can I resolve this?

Answer 1

There are a few things that you need to do to get the result. First, it looks like your JOIN on customer_tags isn't using the correct columns. You've got:

ON `customer_tags_ids`.`customer_tags_ids_id` = `customer_tags`.`customer_tags_id`

And from your data it looks like you need:

ON `customer_tags_ids`.`customer_tags_id` = `customer_tags`.`customer_tags_id`

Then since you want to use GROUP_CONCAT on multiple columns, you'll need to use DISTINCT when concatenating the data:

SELECT  
  c.`customers_id`, 
  c.`customer_name`,
  GROUP_CONCAT(DISTINCT CONCAT(cp.`firstname`, ' ', cp.`lastname`) SEPARATOR ', ') as CustomerName,
  GROUP_CONCAT(DISTINCT ct.customer_tag_name ORDER BY ct.customer_tag_name SEPARATOR ', ') as CustomerTags
FROM `customers` c
INNER JOIN `customer_persons` cp
  ON c.`customers_id` = cp.`customers_id`
LEFT OUTER JOIN `customer_tags_ids` cti
  ON c.`customers_id` = cti.`customers_id`
LEFT OUTER JOIN `customer_tags` ct
  ON cti.`customer_tags_id` = ct.`customer_tags_id`
-- WHERE c.`customers_id` = 1
GROUP BY c.`customers_id`, c.`customer_name`
ORDER BY c.`customers_id`, c.`customer_name` asc;

See SQL Fiddle with Demo. This gets the result:

| CUSTOMERS_ID | CUSTOMER_NAME |                CUSTOMERNAME |                 CUSTOMERTAGS |
|--------------|---------------|-----------------------------|------------------------------|
|            1 |    Customer 1 |   Mehmet Yaman, Zafer Zorlu |                Google, Yahoo |
|            2 |    Customer 2 | Serkan Eryaman, Nedim Yaman | Aol, Facebook, Google, Yahoo |