There is a table of goods (shoes) and their availability:

Goods: id title description price Availability: goodsId size count

The question is as follows: I need to display all the product fields in one line and add all available dimensions to this line with the quantity in stock.

  • 2
    On SQL this is impossible. the number of columns returned by the query cannot be created dynamically. the number of columns is always hard wired in the request. This task should be solved on the client, collecting horizontal data as it is read vertically located from the database - Mike

1 answer 1

Use grouping of results and processing them using GROUP_CONCAT:

 SELECT Goods.id, Goods.titile, Goods.description, Goods.price, GROUP_CONCAT(Availability.size, ":", Availability.count, "\n") available FROM Goods JOIN Availability ON Availability.goodsId = Goods.id GROUP BY Goods.id;

In the available column you will have the sizes and quantity through ":", and the transition to the new line will act as a separator between the sizes.

You can also use a query that will return several results of the same variant (in the case of several sizes) and you will need to group the data by id directly by the recipient in an associative array.

 SELECT Goods.id, Goods.titile, Goods.description, Goods.price, Availability.size,Availability.count FROM Goods JOIN Availability ON Availability.goodsId = Goods.id