Mysql查询加入三个表 [英] Mysql query to join three tables

问题描述

我正在使用这个查询:

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

产生这个结果:

sales_id  | bus_title      |cat_id
----------|----------------|------------
 1        | Business 1     | 6  
 2        | Business 12    | 12
 3        | Business 123   | 25

我将字段 cat_id 更改为名为 tb_sales_category 的新表，其中包含字段 sales_category_id、sales_id、cat_id>.如何通过加入此表来编写新查询，以获得与上述相同的结果?

I changed the field cat_id into a new table named tb_sales_category which 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?

我对数据库有点陌生，需要帮助.提前致谢

I am kind of new to databases, need help. Thanks in advance

推荐答案

试试这个:

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

这个想法相当简单，新表 tb_sales_category 中的第一个字段 sales_category_id 用作 代理键，它有与其他两个表之间的关系无关.然后我们来到另外两个字段，分别是sales_id、cat_id，这些你应该映射到关系的另外两个方面.

The idea is fairly simple, the first field in your new table tb_sales_category which is sales_category_id is 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.

你不能在新模式上Join tb_category b ON a.cat_id = b.cat_id 因为我们不再有 a.cat_id，新模式来了table tb_sales_category 角色，通过将其插入两个绑定边，一个带有 INNER JOIN tb_category b ON s.cat_id = b.cat_id 另一个带有 INNER JOIN tb_sales_categorys ON a.sales_id = s.sales_id 我们应该完成了.

You can't Join tb_category b ON a.cat_id = b.cat_id on the new schema becouse we no longer have a.cat_id, and here comes the new table tb_sales_category role, by inserting it with two binding sides, one with INNER JOIN tb_category b ON s.cat_id = b.cat_id and the other with INNER JOIN tb_sales_category s ON a.sales_id = s.sales_id we should be done.

希望这是有道理的.

这篇关于Mysql查询加入三个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！