MySQL选择相关表中有多个匹配记录的记录

Question

我有 2 个表:

pages 表包含字段 id

pages2categories 表包含字段 page 和 category

我想从pages表中选择记录

where (there is a record in the pages2categories table 
WHERE pages2categories::page = pages::id AND pages2categories:: category = 'X') 
AND (there is also a record in the pages2categories table WHERE pages2categories::page = pages::id AND pages2categories:: category = 'Y') 
AND (there is also a record in the pages2categories table WHERE pages2categories::page = pages::id AND pages2categories:: category = 'Z')

换句话说，仅当所有三个匹配记录都存在于 pages2categories 表中时，才从 pages 表中返回一条记录。

示例:

页面::id

1

2

3

pages2categories::page, pages2categories::category

1, x

1, y

2, x

2, y

2, z

3, x

结果应该只返回 pages 表中的记录 2。

我希望这是清楚的。谢谢!

Answer 1

你也可以使用这个:

SELECT * FROM TABLE1 A
WHERE EXISTS (SELECT * FROm TABLE2 B WHERE A.ID = B.ID AND B.category = 'X') 
AND EXISTS (SELECT * FROm TABLE2 B WHERE A.ID = B.ID AND B.category = 'Y')
AND EXISTS (SELECT * FROm TABLE2 B WHERE A.ID = B.ID AND B.category = 'Z')