GROUP_CONCAT being weird

Question

I use three tables with an inner join on 'user.ID'='email.ID' and 'user.ID'='telephone.ID'.

Every user has a name and an ID. Each email has a username, the email address itself and a corresponding userID. Each telephone number has a username, the number itself and a corresponding userID. It is possible for a user to have any amount of email addresses and telephone numbers.

I managed to output a users email addresses by selecting them like this:

GROUP_CONCAT(email.Address SEPARATOR ',') AS Address,
GROUP_CONCAT(email.Username SEPARATOR ',') AS eUsername

I then converted it into an array:

$Address = explode(",", $row["Address"]);
$eUsername = explode(",", $row["eUsername"]);

And printed it like this:

  for($i = 0;$i<count($Address);$i++) {
    echo $eUsername[$i] .': '. $Address[$i].'<br>';
  }

This is the printed table:

I then just did the same thing with the telephone numbers

Selecting:

GROUP_CONCAT(telephone.Number SEPARATOR ',') AS Number,
GROUP_CONCAT(telephone.Username SEPARATOR ',') AS tUsername

Converting:

$Number = explode(",", $row["Number"]);
$tUsername = explode(",", $row["tUsername"]);

Printing:

for($i = 0;$i<count($Number);$i++) {
    echo $tUsername[$i] .': '. $Number[$i].'<br>';
  }

But this messed up the whole table:

I don´t get why it prints the email addresses three times and just doesn´t print anything in the 'Telephone' coloumn although the second for loop is echoed into the most right coloumn.

Sorry for the long post and thanks for any help.

EDIT:

Here is my full SQL query:

SELECT user.Name AS Name, user.ID AS ID,
GROUP_CONCAT(email.Address SEPARATOR ',') AS Address,
GROUP_CONCAT(email.Username SEPARATOR ',') AS eUsername,
GROUP_CONCAT(telephone.Number SEPARATOR ',') AS Number,
GROUP_CONCAT(telephone.Username SEPARATOR ',') AS tUsername
FROM user
INNER JOIN email ON user.ID=email.ID
INNER JOIN telephone ON user.ID=telephone.ID
WHERE Name REGEXP '$searchterm'
GROUP BY ID

The WHERE statement is not final. This is just for testing.

That's likely because of how you join the tables. But you have to provide the actual SQL query for us to verify that. — Dormilich, Jul 26 '18 at 08:27
Possible duplicate of [Strange duplicate behavior from GROUP\_CONCAT of two LEFT JOINs of GROUP\_BYs](https://stackoverflow.com/questions/45250646/strange-duplicate-behavior-from-group-concat-of-two-left-joins-of-group-bys) — philipxy, Jul 26 '18 at 10:32
Please read the edit help re code blocks vs code snippets. You want the former. Please use text, not images/links, for text including tables. Please read & act on [mcve]. Also this is a faq. Always google many clear concise phrasings of your question/problem/goal/issue. See [ask] & the downvote arrow mouseover text. PS You didn't "do the same thing" the second time, because the left table was not the same, it was the output from the first join. You want the join of two separate join-plus-aggregates. Although in this pariticular case there's a solution with just one group concat unique. — philipxy, Jul 26 '18 at 10:35

score 0 · Accepted Answer · answered Jul 26 '18 at 10:24

The simplest solution is to use distinct:

SELECT u.Name AS Name, u.ID AS ID,
       GROUP_CONCAT(DISTINCT e.Address SEPARATOR ',') AS Address,
       GROUP_CONCAT(DISTINCT e.Username SEPARATOR ',') AS eUsername,
       GROUP_CONCAT(DISTINCT t.Number SEPARATOR ',') AS Number,
       GROUP_CONCAT(DISTINCT t.Username SEPARATOR ',') AS tUsername
FROM mitglied u INNER JOIN
     email e
     ON u.ID = e.ID INNER JOIN
     telephone t
     ON u.ID = t.ID
WHERE u.Name REGEXP '$searchterm'
GROUP BY u.ID, u.name;

Notes:

I assume that mitglied is users.
Table aliases make the query easier to write and to read.
Qualified column names are recommended whenever you have multiple table references.

GROUP_CONCAT being weird

1 Answers1