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，并且通过插入具有两个绑定面的一个新表tb_sales_category角色出现了，其中一个带有INNER JOIN tb_category b ON s.cat_id = b.cat_id，另一个带有使用INNER JOIN tb_sales_category s 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屋！