MySQL Concat query alternative

Question

I have database table :

+----+--------+-----------+
| id |  name  | know_from |
+----+--------+-----------+
|  1 | Andy   |           |
|  2 | Tony   | Andy      |
|  3 | Ben    | Andy      |
|  4 | Miller | Ben       |
|  5 | Bob    | Tony      |
|  6 | Scott  | Andy      |
+----+--------+-----------+

How to effectively query it and expecting the result as below :

+----+--------+-------+------------------+
| id |  name  | total |  reference_list  |
+----+--------+-------+------------------+
|  1 | Andy   |     3 | Tony, Ben, Scott |
|  2 | Tony   |     1 | Bob              |
|  3 | Ben    |     1 | Miller           |
|  4 | Miller |     0 |                  |
|  5 | Bob    |     0 |                  |
|  6 | Scott  |     0 |                  |
+----+--------+-------+------------------+

I have a solution using GROUP_CONCAT( know_from SEPARATOR ',') but the server is still using MySQL ver 5.1 which doesn't support this syntax. Any query alternative? Thanks in advance!

P.S.) Don't tell me to update the version as solution

You might want to post your whole query solution to make it clearer on where is the problem — Andreas
– Andreas, Commented Jul 22, 2019 at 4:22
As far as I can see, MySQL 5.1. does support that syntax. Unless you include the comma. Then AFAIK no version supports it. i.e. GROUP_CONCAT(name SEPARATOR ',') should work, GROUP_CONCAT(name, SEPARATOR ',') can't possibly. — Amadan
– Amadan, Commented Jul 22, 2019 at 4:28
Docs say it does. Then again, I can't test it - I'm certainly not going to install 5.1. :P — Amadan
– Amadan, Commented Jul 22, 2019 at 6:01

Andrews B Anthony · Accepted Answer · 2019-07-22 04:35:24Z

2

try concating the name string with the separator before the group concat

group_concat(concat(name,','))

And remove the last , from the resultant string.

edited Jul 22, 2019 at 4:35

answered Jul 22, 2019 at 4:24

Andrews B Anthony

1,38110 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nemuroito Over a year ago

Thanks, I use GROUP_CONCAT(know_from) instead without SEPARATOR

PHP Geek · Accepted Answer · 2019-07-22 04:57:49Z

0

try this:

group_concat(`name` separator ',') as Name

answered Jul 22, 2019 at 4:57

PHP Geek

4,0632 gold badges19 silver badges32 bronze badges

Collectives™ on Stack Overflow

MySQL Concat query alternative

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related