Select from multiple tables - One to Many relation

Question

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 WHERE IN 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

You can have it with just one query with left joins, although it will not show you the result as your pseudo-array, it will be the info of product table with its equivalent joined value from other tables. Would that be ok for you? — Jorge Campos, Sep 16 '16 at 04:10
This is the reason people use ORM's like those found in most popular php frameworks. Take a look at Doctrine2, Eloquent in Laravel or Propel. Essentially you setup mapping classes that will also let you specify the joins. Then you can use objects in your code and the ORM will typically load the relations for you, so you don't have to be constantly concerned in your code with the writing sql statements and can work more naturally with classes of objects that represent the tables and their related data. — gview, Sep 16 '16 at 04:12
@gview I don't see how orms uncomplicates this. We then struggle to help a user get orms to work — Drew, Sep 16 '16 at 04:17
@Drew I work with much more complicated schemas than this all the time. This is a simple schema, Product table, with several 1-M related tables. Set this up in Doctrine2 classes, and getting a page with the result would literally pop out with a single Doctrine2 repository call. The images, prices and Quantities data would be available from the Product object. The user wanted something to simplify further development and that is what an ORM can provide. — gview, Sep 16 '16 at 04:23
@gview two weeks from now, the op is off on symphony doctrine etc, can't get a moderately complex query to fire. No `mysql` peer helps him on something that should be raw. What is the point? We all have strong opinions on this. I reserve the right to be wrong, and respect differing views. Some of us get tired getting orms to work in areas they don't. Like Doctrine with FK's and special cases. Orms take it just so far. Then they barf — Drew, Sep 16 '16 at 04:26
And throw me a life line in [this Answer](http://stackoverflow.com/a/38235437) . We try — Drew, Sep 16 '16 at 04:32
@Drew: You are right that there are queries that are either inefficient or hard as hell to do with the built-in query language that comes along with ORM's. In Doctrine2, that is why you drop down to DBAL from time to time and just roll some sql. — gview, Sep 16 '16 at 04:45
I don't use `MySQL`, yet I know in `T-SQL` there are hints to output result as `XML`. E.g. `SELECT name FROM master.sys.tables FOR XML AUTO`. Maybe `MySQL` has similar feature but for `JSON`? Also, I suggest using `WHERE EXISTS SELECT(...` instead of `WHERE Id IN(...` — Gabrielius, Sep 19 '16 at 18:45
Just to note that having this in XML structure would be best for me, recently I red that JSON is available as data sctructure in mysql, but for now it's pretty buggy and slow, so I will stay with Amr answer for now. Yet, still interesting choice in future. — Maciej Kwas, Oct 05 '16 at 20:14

score 8 · Accepted Answer · answered Sep 20 '16 at 00:12

This query will do the trick for you:

SELECT product.id, product.name, 
(SELECT group_concat(CONCAT('["',images.url, '",',  images.order_number,']')) FROM images WHERE images.product_id = product.id GROUP BY (product.id)) AS IMAGES_LIST,
(SELECT GROUP_CONCAT(CONCAT('["',prices.combination, '","', prices.currency, '",', prices.price,"]" )) FROM prices WHERE prices.product_id = product.id GROUP BY (product.id)) AS PRICE_LIST,
(SELECT GROUP_CONCAT(CONCAT('["',quantites.combination, '",',  quantites.quantity,"]")) FROM quantites WHERE quantites.product_id = product.id GROUP BY (product.id)) AS Quantity_LIST
FROM product WHERE product.id = 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

Love you! :) I have created simple Query-to-tree query builder and it works like a charm saving me tons of work. Thank you — Maciej Kwas, Sep 20 '16 at 02:14
How would this extend if I have multiple levels of one to many relations? 1 A has many B and every B has many C. — The Coding Wombat, Apr 10 '20 at 21:27

score 0 · Answer 2 · answered Sep 19 '16 at 07:33

It will be simpler (and more efficient if foreign keys are set up) to use JOINs than WHERE ... IN ....

The tables could be individually queried to get the required data for Product Id = 1 like this:

SELECT i.*
FROM Product prd
INNER JOIN Images i
ON prd.Id = i.Product_Id
WHERE Product_Id = 1;

SELECT prc.*
FROM Product prd
INNER JOIN Prices prc
ON prd.Id = prc.Product_Id
WHERE Product_Id = 1;

SELECT q.*
FROM Product prd
INNER JOIN Quantities q
ON prd.Id = q.Product_Id
WHERE Product_Id = 1;

score 0 · Answer 3 · answered Sep 19 '16 at 16:49

I would suggest you to have a single result set containing all these information for a ProductID and parse it in PHP. It will make the things simpler.

SELECT prd.ProductId, prd.Name, i.Url, i.Ordernumber, prc.Combination, prc.Currency, prc.Price,q.Combination, q.Quantity FROM Product prd INNER JOIN Images i ON prd.Id = i.Product_Id INNER JOIN Prices prc ON prd.Id = prc.Product_Id INNER JOIN Quantities q ON prd.Id = q.Product_Id WHERE Product_Id = 1;

Select from multiple tables - One to Many relation

3 Answers3

Linked