MySql根据另外两个表的条​​件在一个表中查找数据 [英] MySql find data in one table based on conditions of two other tables

问题描述

通过以下查询帮助我: 我有3个具有这种结构的表.

Help me out with this query: I have 3 tables with this structure.

items_to_groups (item_id | group_id)

items_to_groups (item_id | group_id)

item_to_regions (item_id | region_id)

item_to_regions (item_id | region_id)

项目 [一列]

我需要选择 item 表中与 item_to_groups 表中的item_id匹配的每一行，其中 group = x 并且 item_to_regions 表上的item_id匹配，其中 region = y

I need to select every row on the item table that has an item_id match on item_to_groups table WHERE group = x AND has an item_id match on item_to_regions table WHERE region = y

目前，我拥有的代码是一个带有循环和所有内容的可怕子查询.

Currently the code I have is a horrible subquery with loops and all.

有什么更好的方法? 我曾经考虑过JOIN之类的方法，但实际上并不能完全理解如何做到这一点.

What would be a better way of doing this? I've thought about JOIN and such, but can't really get my head around on how to do it.

推荐答案

SELECT bunch_of_columns
FROM items i 
INNER JOIN items_to_groups ig ON i.id=ig.item_id
INNER JOIN items_to_regions ir on i.id=ir.item_d
WHERE ir.region_id=y
AND ig.group_id=x

查看有关MySQL的 JOIN 文档.联接对于关系数据库很重要. 正如您所说的，您很难掌握联接，请查看有关SQL连接的可视解释，作者是Jeff Atwood.也许有帮助.

Have a look at the JOIN documentation on MySQL. Joins are important for relational databases. As you said you have a hard time grasping joins, have a look at A Visual Explanation of SQL Joins by Jeff Atwood. Maybe it helps.

这篇关于MySql根据另外两个表的条​​件在一个表中查找数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！