Mysql查询加入三个表
我正在使用这个查询:
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.
希望这是有道理的.
相关文章