Search code examples
mysqlsqlselectjoingroup-concat

Selecting array of options as part of select statement

I have this 2 tables:

Options

id, name, created_at, ...

Options table have many sub-options

SubOptions

option_id, name, created_at, ....

I was wondering if this possible to retrieve all of the SubOptions as part of a query select on OPTIONS , by using join (or anything else?) when the OPTION is grouped.

For example:

Select * 
from Options 
JOIN SubOptions ON Options.id = SubOptions.option_id 
GROUP BY Options.name

Now i know i can access SubOptions.name , but is there any way to access all of SubOptionss with option_id equal to option_id ?

Solution

  • Use GROUP_CONCAT() function to group sub option names

    Try this: 

    SELECT O.name, GROUP_CONCAT(SO.name) subOptionName
FROM Options O 
LEFT JOIN SubOptions SO ON O.id = SO.option_id 
GROUP BY O.name;