CODEWITHSUNDEEP
mysqlsql
Stavros
Stavros

Reputation: 665

MySQL, select multiple rows as one based on foreign key

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.

Upvotes: 3

Views: 1666

Answers (2)

ItalianStallion
ItalianStallion

Reputation: 296

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

Upvotes: 0

mreiterer
mreiterer

Reputation: 556

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

Upvotes: 4

Related Questions