Combine Multiple child rows into one row MYSQL

by

The easiest way would be to make use of the GROUP_CONCAT group function here..

select
  ordered_item.id as `Id`,
  ordered_item.Item_Name as `ItemName`,
  GROUP_CONCAT(Ordered_Options.Value) as `Options`
from
  ordered_item,
  ordered_options
where
  ordered_item.id=ordered_options.ordered_item_id
group by
  ordered_item.id

Which would output:

Id              ItemName       Options

1               Pizza          Pepperoni,Extra Cheese

2               Stromboli      Extra Cheese

That way you can have as many options as you want without having to modify your query.

Ah, if you see your results getting cropped, you can increase the size limit of GROUP_CONCAT like this:

SET SESSION group_concat_max_len = 8192;

More Related Contents:

  1. How can I select data from different tables?
  2. What is the default MySQL JOIN behaviour, INNER or OUTER?
  3. MySQL cartesian product between two SELECT statements
  4. Recreating a query using INNER JOIN? [duplicate]
  5. MySQL – UPDATE query based on SELECT Query
  6. MySQL: Quick breakdown of the types of joins [duplicate]
  7. Required to join 2 tables with their FKs in a 3rd table
  8. MySQL join with where clause
  9. MySQL SELECT WHERE datetime matches day (and not necessarily time)
  10. MySQL Query – Records between Today and Last 30 Days
  11. Ordering by specific field value first
  12. CROSS/OUTER APPLY in MySQL
  13. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  14. INSERT INTO…SELECT for all MySQL columns
  15. Cumulative sum over a set of rows in mysql
  16. MySql Count cannot show 0 values
  17. MySQL select statement with CASE or IF ELSEIF? Not sure how to get the result
  18. MySQL difference between two rows of a SELECT Statement
  19. MySQL JOIN with LIMIT 1 on joined table
  20. MySQL – How to search for exact word match using LIKE?
  21. How do I get the first day of the week of a date in mysql?
  22. Converting SELECT DISTINCT ON queries from Postgresql to MySQL
  23. Laravel join with 3 Tables
  24. MySQL Insert & Joins
  25. SELECT query return 1 row from each group
  26. How to join two tables using a comma-separated-list in the join field
  27. SELECT INTO Variable in MySQL DECLARE causes syntax error?
  28. MySQL Error: #1142 – SELECT command denied to user
  29. When to use a left outer join?
  30. MySQL Select Multiple VALUES

Leave a Comment