Mysql join and group by question

[ennio2]

Hi all!

I have joined two tables and groupped by a specific id.

table_one

id       | name               |   
-----------------------------------
1        | something1         | 
2        | something2         |
3        | something3         | 
4        | something4         | 
5        | something5         | 
6        | something6         | 
...and so on...

table_two

id       | table_one_ID   |some_id
----------------------------------------
1        | 1              | 50
2        | 1              | 87
3        | 2              | 50
4        | 2              | 87

stmt = $pdo->prepare("SELECT table_one.id, table_one.name, table_two.table_one_ID, table_two.some_id FROM table_one LEFT JOIN table_two ON table_one.id = table_two.table_one_ID GROUP BY table_one.id ORDER BY table_one.id DESC LIMIT 20");
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($result as $output):

 echo $output["some_id"];

endforeach;

When I try to echo $output[“some_id”] it only outputs one value but I’d like to get all the values.

Of course, if id do not group by table_one’s ID it outputs the other values but it duplicates the row.

Can someone help me with this?

[chorn]

i don’t get what you expect, because a database query mostly returns two-dimensional data, so a table - how should that look like in your case? but you can aggregate IDs into a list if that helps

https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

[m_hutley]

@ennio2

When I try to echo $output[“some_id”] it only outputs one value but I’d like to get all the values.

@ennio2

I have joined two tables and groupped by a specific id.

These two statements are mutually exclusive.
By grouping, you select 1 row per group. If you’re trying to ORDER the results, you should order them, not group them. You might be trying to GROUP_CONCAT instead…