MySQL INNER JOIN with multiple values in a column

Question

Does anyone knows how to do a mysql inner join with multiple values from a table in a column, I mean:

t_1: id_t1 | name
001 | name_value

t1_t2: id_t1 | id_t2
              001 | 020
              001 | 030
              001 | 050

Then, a query that return me for example, something like this:

id_t1 | name | values_t_2
001 | name_value | value1, value2, value4

If anyone can tell me a way to do this, I'd be grateful.

Colin M · Accepted Answer · 2013-02-19 21:19:22Z

5

SELECT
    t1.id_t1,
    t1.name,
    GROUP_CONCAT(t2.value SEPARATOR ', ') AS values_t_2
FROM
    t_1 t1
    INNER JOIN t1_t2 t1t2 ON (t1.id_t1 = t1t2.id_t1)
    INNER JOIN t_2 t2 ON (t1t2.id_t2 = t2.id_t2)
GROUP BY
    t1.id_t1

answered Feb 19, 2013 at 21:19

Colin M

13.4k3 gold badges42 silver badges61 bronze badges

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

3 Comments

Colin M Over a year ago

@Sebas There is no standard SQL way to do this. Each SQL server implements its own mechanism.

Sebas Over a year ago

this is correct, as of the group_concat thing. But I was refering to the GROUP BY part.

Colin M Over a year ago

Because t1.name isn't wrapped in an aggregate function and therefore wouldn't work on most SQL servers?

Collectives™ on Stack Overflow

MySQL INNER JOIN with multiple values in a column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related