SQL嵌套查询 - 第2部分

Just the other day I asked a question about nesting queries together - in a nutshell it was about how to pull data from two different related tables when there may or may not be linked rows in a second table. That led me down the path to LEFT/RIGHT JOINS and was easy to pick up. Now, however I am coming across an entirely new problem, and this is stumping me.

When one pulls back information using LEFT/RIGHT JOINS (let's assume that it's just a LEFT JOIN for the rest of this question), in the scenario where there are multiple matching rows in Table B, Table A's information is pulled back each time a match in Table B is found, and this results in entirely new rows being pulled back as well. Is there a way to aggregate the information from Table B when doing LEFT JOINS?

For example, let's say that we have a products table with only one product row in it and an images table, which has 6 image rows, all linked to the one product in the products table.

Let's say that we then use the query:

SELECT product_name, large_image, medium_image FROM products
LEFT JOIN images ON images.image_product_Id = products.productId

This will pull back 6 rows, with repeated information for the product_name in each row, but unique information for the large_image and medium_image.

Is there a way to pull back this information in one row, with the rows from the images table nested? The result returning one row, and the image information for the 6 images in an associative array that is pulled back as a column?

Ideally, this information would come back as :

[
  1 => [ product_name => 'Apple iPhone',
         images => [
         {
            large_image=>'image1.jpg',
            'medium_image'=>'image1_m.jpg'
         },
         {
            large_image=>'image2.jpg',
            medium_image=>'image2_m.jpg'}
            ...
         ]
       ]
]

The only solution I can currently think of is to pull the information for the products back in one query:

SELECT product_name, productId FROM products

and while iterating over that information, making subsequent queries to the database for each row to get all the images for that product:

SELECT large_image, medium_image FROM images WHERE images.image_product_Id 
= '$productId'

where '$productId' is the parameter I am feeding the second query, and which represents the productId column from the first query. This seems not only inefficient, but bad programming practice. Could someone point me in the right direction?

I hope I was able to word this clearly - if not, please let me know what I can do word it better!

Thanks in advance :)

As per your question I found an answer that works for you, see the below SQL.

SELECT product_name, 
       GROUP_CONCAT(DISTINCT large_image) AS LargeImages,
       GROUP_CONCAT(DISTINCT medium_image) AS medium_images 
FROM products 
LEFT JOIN images ON images.image_product_Id = products.productId 
GROUP BY productId