Maciej Kwas Maciej Kwas - 1 year ago 88
MySQL Question

Select from multiple tables - One to Many relation

I have such tables:

Table Product

[ Id | Name ]

Table Images

[ Product_Id | Url | Ordernumber]

Table Prices

[ Product_Id | Combination | Currency | Price]

Table Quantites

[ Product_Id | Combination | Quantity]

Table Product is in relation one-to-many with other tables. I need to query the table and result something like this (pseudo-array):

ProductId: 1,
Name: 'Sample product',
Images: [
[url, 1],
[url, 2]
Prices: [
[aaa, USD, 50.00],
[aaa, EUR, 50.00],
[bbb, USD, 59.00],
[bbb, EUR, 59.00]
Quantities: [
[aaa, 5],
[bbb, 3]

The way I'm doing it now is as follows:
I query all the products, list their id's, and then query each table (images,prices,quantities) with
clause. When I have all the data I start to parse the tables in php to get desired structure.

I wonder if there is some better way to extract those data, I have many different tables like this and creating configuration parser for each of them is a bit messy and problematic. Is there a possibility that mysql will take some burden from me?

thank you

Answer Source

This query will do the trick for you:

(SELECT group_concat(CONCAT('["',images.url, '",',  images.order_number,']')) FROM images WHERE images.product_id = GROUP BY ( AS IMAGES_LIST,
(SELECT GROUP_CONCAT(CONCAT('["',prices.combination, '","', prices.currency, '",', prices.price,"]" )) FROM prices WHERE prices.product_id = GROUP BY ( AS PRICE_LIST,
(SELECT GROUP_CONCAT(CONCAT('["',quantites.combination, '",',  quantites.quantity,"]")) FROM quantites WHERE quantites.product_id = GROUP BY ( AS Quantity_LIST
FROM product WHERE = 1
  • First get the products
  • for each one using a sub-query we get the related images, and using group concat we can get them in one field
  • same thing for the prices and quantities

Query Result

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download