MySQL, select multiple rows as one based on foreign key

Question

i have the following 2 tables.

| ID | NAME | AGE |
|----|------|-----|
|  0 | John |  30 |
|  1 | Nick |  35 |
|  2 | Mike |  30 |

| USERID |      FRUIT |
|--------|------------|
|      0 |      apple |
|      0 |     orange |
|      1 |     banana |
|      1 |     tomato |
|      1 |      grape |
|      1 | watermelon |
|      2 |       pear |
|      2 |     cherry |

I'm using this query in order to get what fruit every user with age<34 likes.

SELECT users.name, fruit FROM users,fruits WHERE users.id=fruits.userid AND users.age<34;

Result:

| NAME |  FRUIT |
|------|--------|
| John |  apple |
| John | orange |
| Mike |   pear |
| Mike | cherry |

sqlfiddle

Is there a way to have only one row returned for every user with the fruits on one or multiple columns?

Desired result:

| NAME |     FRUIT     |
|------|---------------|
| John |  apple,orange |
| Mike |   pear,cherry |

or

| NAME |  FRUIT | FRUIT  |
|------|--------|--------|
| John |  apple | orange |
| Mike |   pear | cherry |

I tried using GROUP_CONCAT() but i didn't get the result i expected, any help would be great.

Thanks in advance for your time.

As i said on the main post, i did some tests using GROUP_CONCAT() like `SELECT users.name, group_concat(fruit) from users,fruits where users.id=fruits.userid AND users.age<34;`, also i tried using `group by`(?) but i don't think this was on the right track. I'm not so familiar with the sql syntax so i didn't have many ideas, any links with info on the direction i should search would be great as well! — Stavros, Oct 08 '14 at 21:16
Side note on your `JOIN` syntax: http://stackoverflow.com/questions/334201/why-isnt-sql-ansi-92-standard-better-adopted-over-ansi-89 — Michael McGriff, Oct 08 '14 at 21:20

score 4 · Accepted Answer · answered Oct 08 '14 at 21:14

4

SELECT users.name, group_concat( fruit )
FROM users,fruits 
WHERE users.id=fruits.userid AND users.age<34
group by user.name

answered Oct 08 '14 at 21:14

mreiterer

556
1
4
15

Thanks, this did it. How should i change the query in order to add data (with a similar form) from a 3rd table? I tried `SELECT users.name, group_concat( fruit ), group_concat( vegetable ) FROM users,fruits,vegetables WHERE users.id=fruits.userid AND users.id=vegetables.userid AND users.age<34 group by users.name` but it didn't work as i expected. – Stavros Oct 08 '14 at 21:28
Try this : GROUP_CONCAT(CONCAT_WS(' ', fruit, vegetable)) – mreiterer Oct 08 '14 at 21:36
Didn't work so good, i guess i am doing something wrong with the rest query. Here is a fiddle http://sqlfiddle.com/#!2/d87e3b/18 – Stavros Oct 08 '14 at 21:46
1

Does this help ? SELECT users.name, GROUP_CONCAT( distinct fruit ), GROUP_CONCAT( distinct vegetable) FROM users,fruits,vegetables WHERE users.id=fruits.userid AND users.id=vegetables.userid AND users.age<34 group by users.name – mreiterer Oct 09 '14 at 05:37

score 0 · Answer 2 · answered Oct 08 '14 at 22:06

Try This(The STUFF function):

CREATE TABLE #Test(
    Name    VARCHAR(100),
    FRUIT   VARCHAR(100)
)

INSERT INTO #Test VALUES
    ('John', 'apple'),
    ('John', 'orange'), 
    ('Mike', 'pear'),
    ('Mike', 'cherry')

SELECT Name,
       STUFF((SELECT ', ' + FRUIT
              FROM #Test t1
              WHERE t1.Name = t2.Name 
              FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)'),1,2,'') AS 'Fruits'
FROM #Test t2
Group by Name

Drop Table #Test

This outputs:

Name       Fruits
John    apple, orange
Mike    pear, cherry

MySQL, select multiple rows as one based on foreign key

2 Answers2