MySQL Mysql查询加入三个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10257433/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql query to join three tables
提问by Jimmy M
I am using this query:
我正在使用这个查询：
SELECT a.sales_id, d.bus_title, a.cat_id
FROM tbl_sales a
INNER JOIN tb_category b ON a.cat_id = b.cat_id
INNER JOIN tbl_business d ON d.bus_id = a.bus_id
which produces this result:
产生这个结果：
sales_id | bus_title |cat_id
----------|----------------|------------
1 | Business 1 | 6
2 | Business 12 | 12
3 | Business 123 | 25
I changed the field cat_id into a new table named tb_sales_categorywhich contains the fields sales_category_id, sales_id, cat_id. How can I write the new query by joining this table too to, get the same result as above?
我将字段 cat_id 更改为一个名为的新表tb_sales_category，其中包含字段sales_category_id、sales_id、cat_id。如何通过加入此表来编写新查询，以获得与上述相同的结果？
I am kind of new to databases, need help. Thanks in advance
我对数据库很陌生，需要帮助。提前致谢
采纳答案by Mahmoud Gamal
Try this:
尝试这个：
SELECT a.sales_id, d.bus_title, s.cat_id
FROM tbl_sales a
INNER JOIN tb_sales_category s ON a.sales_id = s.sales_id
INNER JOIN tbl_business d ON a.bus_id = d.bus_id
INNER JOIN tb_category b ON s.cat_id = b.cat_id
The idea is fairly simple, the first field in your new table tb_sales_categorywhich is sales_category_idis working as a surrogate key, it has nothing to do with the relations between the two other tables. Then we come to the other two fields which are sales_id, cat_id, these what you should map to the other two sides of the relations.
这个想法很简单，在新表中的第一个字段tb_sales_category是sales_category_id工作作为代理键，它没有任何与其他两个表之间的关系。然后我们来到另外两个字段，它们是sales_id，cat_id，这些你应该映射到关系的另外两个方面。
You can't Join tb_category b ON a.cat_id = b.cat_idon the new schema becouse we no longer have a.cat_id, and here comes the new table tb_sales_categoryrole, by inserting it with two binding sides, one with INNER JOIN tb_category b ON s.cat_id = b.cat_idand the other with INNER JOIN tb_sales_category s ON a.sales_id = s.sales_idwe should be done.
你不能Join tb_category b ON a.cat_id = b.cat_id在新模式上，因为我们不再有a.cat_id，新的表tb_sales_category角色来了，通过插入它的两个绑定边，一个 withINNER JOIN tb_category b ON s.cat_id = b.cat_id另一个 withINNER JOIN tb_sales_category s ON a.sales_id = s.sales_id我们应该完成。
Hope this makes sense.
希望这是有道理的。
回答by gimg1
I am not a great fan of inner joins so try this:
我不是内连接的忠实粉丝，所以试试这个：
SELECT a.sales_id, a.cat_id, c.bus_title
FROM tb_sales_category a, tb_sales b, tbl_business c
WHERE a.sales_id = b.sales_id
AND b.bus_id = c.bus_id
