CODEWITHSUNDEEP
mysqlsqlselect
IsraGab
IsraGab

Reputation: 5185

Return row field on a condition

Is it possible in a SQL request to do such a thing:

In 1st table (Product), I have the folowing:

id         name
1          Table
2           Chair

In 2nd table (color), I have the folowing:
id        productID           color
 1                   1                  red
2                   1                green
3                   2                 white

I would like to get for each product a list that tell me in wich color is exist in my dbb. For example, I would like that the SELECT returns 2 rows: in the 1rst row: Table, red, green in the second row: chair, white. thank you

Upvotes: 0

Views: 56

Answers (2)

Oswald
Oswald

Reputation: 31657

This is not possible. Every result from the result set of a query has the same number of fields.

You can fake it with GROUP_CONCAT, though:

SELECT name, GROUP_CONCAT(c.color)
FROM Product AS p
LEFT OUTER JOIN color AS c ON p.id = c.ProductID
GROUP BY p.name

Upvotes: 1

Mureinik
Mureinik

Reputation: 311393

Using a JOIN will provide the matches between the rows, and GROUP_CONCAT will help you produce a single line list:

SELECT   name, GROUP_CONCAT(color) AS colors
FROM     product
JOIN     color ON product.id = color.product_id
GROUP BY name

Upvotes: 1

Related Questions