MySQL JOIN associative column with multiples row values in array

Question

Table ORDEM

ord_id | name
1      -  'A'
2      -  'B'
3      -  'C'
4      -  'D'

Table ORCAMENTO

orc_id | type
  10   -  'AA'
  20   -  'BB'
  30   -  'CC'
  40   -  'DD'

Table ORDEM_ORCAMENTO

id | ord_id | orc_id  
1  -   1    -   10
2  -   1    -   20
3  -   2    -   30
4  -   2    -   40

MySQL Query:

SELECT o.*, oo.orc_id 
FROM ordem AS o
        INNER JOIN ordem_orcamento AS oo ON oo.ord_id = o.ord_id
ORDER BY o.ord_id ASC

Results Got:

1 - 'A' - 10
1 - 'A' - 20
2 - 'B' - 30
2 - 'B' - 40

Results Needed:

1 - 'A' - 10,20
2 - 'B' - 20,30

I want to put all id's from ORCAMENTO table that belongs to ORDEM table into an array...

I've tried many ways to figure it out but I haven't success...

Just return a result and process the array in PHP

Strawberry
– Strawberry

2014-07-23 18:42:12 +00:00
Commented Jul 23, 2014 at 18:42 — Strawberry
– Strawberry, Commented Jul 23, 2014 at 18:42

FuzzyTree · Accepted Answer · 2014-07-23 18:41:43Z

3

GROUP_CONCAT allows you to select a group into a comma separated string

select o.ord_id, o.name, group_concat(oo.orc_id)
from ordem o
join ordem_orcamento oo on o.ord_id = oo.ord_id
group by o.ord_id, o.name
order by o.ord_id asc

answered Jul 23, 2014 at 18:41

FuzzyTree

32.4k3 gold badges58 silver badges87 bronze badges

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

1 Comment

caiovisk Over a year ago

It's Perfect, I was using GROUP_CONCAT without 'group by' and didn't work fine, But now all is working perfect... Tks

tommy · Accepted Answer · 2014-07-23 18:50:34Z

0

You can build an array with php too, by looping the result

$data = array();
foreach ($result as $res) {
    $data[$res->ord_id][] = $res->orc_id
}

//output: foreach ($data as $orderitem) echo implode(",",$orderitem);

answered Jul 23, 2014 at 18:50

tommy

665 bronze badges

2 Comments

caiovisk Over a year ago

Could be, but I think about performance it wont work really well... Bringing clean results from the database still my best choice for performance...

tommy Over a year ago

yes of course. but if you need the seperate ids for some actions (counting etc), each time you have to explode the string.

Collectives™ on Stack Overflow

MySQL JOIN associative column with multiples row values in array

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related