选择带有子查询的表

Question

我试图列出所有带有产品映像的产品，但不确定如何在这种情况下使用subquery。下面是我的数据库结构

产品

products_id + product_name +
    1       |    Apple     |
    2       |    Banana    |

products_screenshot

products_id +       images       +
    1       |    Apple-1.jpg     |
    1       |    Apple-2.jpg     |
    1       |    Apple-3.jpg     |
    2       |    Banana-1.jpg    |
    2       |    Banana-2.jpg    |
    2       |    Banana-3.jpg    |

这是我的疑问：

$sql = "SELECT p.* FROM `products` p ORDER BY p.products_id ASC ";

//LEFT JOIN (SELECT ps.* FROM `products_screenshot` ps WHERE ps.products_id=p.products_id) AS pss ON(p.products_id=pss.products_id)

$query = $db->query($sql);

while ($row = $query->fetch_object()) {

     // result
     echo $row->products_name.'</br>';

     // list all product screenshot related with this 
     //$row->images

}

Answer 1

根据您的要求，您可以如下所示。然后执行查询并循环通过。$row->图像将分隔为逗号，您可以分割它们，为产品显示它们。

$sql = "SELECT p.products_id,p.product_name,group_concat(pi.images) as `images`
FROM `products` p 
left join products_screenshot  pi on pi.products_id = p.products_id
group by p.products_id
ORDER BY p.products_id ";

如果您不关心产品在循环中重复多次，那么只需在下面使用，对于每个循环，您将得到一个带有产品和相关图像的新行

$sql = "SELECT p.products_id,p.product_name,pi.images
FROM `products` p 
left join products_screenshot  pi on pi.products_id = p.products_id
ORDER BY p.products_id ";

Answer 2

这样一个简单的查询有什么问题：

select
    p.products_id,
    p.product_name,
    ps.images
from
    products p
        join products_screenshot ps
            on p.products_id=ps.products_id
where
    whateveryouline
order by
    1,2

它会给你所有你需要的东西吗？

Answer 3

请尝试以下查询：

select products.product_name, products_screenshot.images from products,products_screenshot  where products.products_id = products_screenshot.products_id