1

I want to retrieve data table in row field has fetch from database. Simply, one to many relationship in result set from stored procedure.

Table 'car'

Id  Car
1   BMW
2   Mercedes
3   Audi

Available in many color

Table 'CarColor'

Id CarId    Color
1   1     Metalic White
2   1     Acrylic White
3   2     Glossy Red
4   2     Glossy White
5   3     Frosty Black
6   3     Frosty White

Consequently, I want result set from stored procedure like

Id  Car       CarColor
1   BMW      'CarColor Datatable' (i.e. Datatable of CarColor table relevant row)
2   Mercedes 'CarColor Datatable' (i.e.  Datatable of CarColor table relevant row)
3   Audi     'CarColor Datatable' (i.e.  Datatable of CarColor table relevant row)
  • Thats not how SQL works in general, unless your RDBMS can transform a subquery or something to JSON on-the-fly. Normal solution is to do a join, which will for each "car" return as many rows as there are the associated "colors" (with the "car" columns duplicated in each row) and then group these the way you want in your application code. – jkavalik Sep 29 '15 at 11:22
1

The closest you can get is to use the GROUP_CONCAT function:

SELECT car.*, GROUP_CONCAT(color SEPARATOR ',')
FROM car
INNER JOIN carColor ON car.id=carColor.carID
GROUP BY carColor.carID;

[sqlfiddle]

Be aware of the group_concat_max_len option that can truncate your concat results.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.