mySQL:连接三个表-如何? [英] mySQL: Joining three tables - how?

问题描述

我的应用程序中有以下查询.它运作良好，但我还需要包含与每个制造商关联的产品数量.

I have the following query in my application. It works well, but I need it to also contain the number of products that are associated with each manufacturer.

当前查询:

SELECT * FROM (`manufacturers`)
JOIN `languages` ON `manufacturers`.`lang` = `languages`.`id`
ORDER BY `languages`.`id` asc, `id` asc

我的产品表格如下:

id    |    name    |    manufacturerid
0     |   Product1 |    0

推荐答案

SELECT `manufacturers`.*, `languages`.*, COUNT(`products`.`id`) AS NumberOfProducts
FROM (`manufacturers`)
JOIN `languages` ON `manufacturers`.`lang` = `languages`.`id`
LEFT OUTER JOIN `products` ON 
      `products`.`manufacturerid` =  `manufacturers`.`manufacturerid`
GROUP BY <Column list for manufacturers AND languages here>
ORDER BY `languages`.`id` asc, `manufacturers`.`id` asc

这篇关于mySQL:连接三个表-如何?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！