MYSQL从另一个表中查询相关数据

Question

我想从另一个表中查询相关数据，在那里我可以获得最低的关联值...

两个表的例子

products
id          name          description
0           product_1     short description of 1
1           product_2     short description of 2

prices
id          product_id          option        price       personal
0           1                   3             10.00       1
1           0                   2             15.00       1
2           1                   3             5.00        0
3           1                   3             8.00        0
4           0                   2             7.00        1

需要输出

id          name          description       price     option
0           product_1     short ...         7.00      2
1           product_2     short ...         10.00     3

我主要尝试进行的查询是获取所有关联字段、从个人 = 1 且价格最低的价格获取关联数据的查询。

当前查询(获取最低价格但不关联选项)

SELECT products.*, prices.option, 
   (SELECT ROUND( MIN( price ), 2) FROM prices WHERE product_id = products.id AND personal = 1) AS price 
FROM products 
ORDER BY price_low ASC

Answer 1

使用连接而不是子查询并按产品分组怎么样？它应该看起来像这样:

SELECT products.*, ROUND(MIN(prices.price), 2), prices.option
FROM products
INNER JOIN prices ON products.id = prices.product_id
WHERE prices.personal = 1
GROUP BY products.id

我在 SqlFiddle 上试过了，here are the results .