CODEWITHSUNDEEP
mysqlconcatenation
nemuroito
nemuroito

Reputation: 179

MySQL Concat query alternative

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

Upvotes: 1

Views: 365

Answers (2)

PHP Geek
PHP Geek

Reputation: 4033

try this:

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

Upvotes: 0

Andrews B Anthony
Andrews B Anthony

Reputation: 1381

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

group_concat(concat(name,','))

And remove the last , from the resultant string.

Upvotes: 2

Related Questions