假设我们有3个表:
| test1 | | test1_to_test2 | | test2 |
|-------+ +----------------| +-------|
| id |-----| test1_id | +----| id |
| test2_id |----+
正是你所拥有的结构.
内容:
test1
+----+-------+
| id | value |
+----+-------+
| 1 | val1 |
| 2 | val2 |
+----+-------+
test1_to_test2
|----------+----------|
| test1_id | test2_id |
|----------+----------|
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
|----------+----------|
test2
|----+
| id |
|----+
| 1 |
| 2 |
|----+
我们需要从test1表中选择值,test1_to_test2中的行包含(test2_id = 1)AND(test2_id = 2).所以,我们想要这个:
+----+-------+
| id | value |
+----+-------+
| 1 | val1 |
+----+-------+
为此,我们将任务分为两个子任务:
1.从test1_to_test2中选择test1_id,它包含两行:
SELECT
test1_id
FROM
test1_to_test2
WHERE
test1_to_test2.test2_id IN (1,2)
GROUP BY
test1_id
HAVING
COUNT(test1_id) = 2
2.使用子查询和IN运算符(它是我们需要的SQL)从test1中选择适当的行:
SELECT
test1.id,
test1.`value`
FROM
test1
WHERE
test1.id IN
(
SELECT
test1_id
FROM
test1_to_test2
WHERE
test1_to_test2.test2_id IN (1,2)
GROUP BY
test1_id
HAVING
COUNT(test1_id) = 2
)
我们得到了我们所需要的:
+----+-------+
| id | value |
+----+-------+
| 1 | val1 |
+----+-------+
对您的表使用相同的方法,您将获得具有“XYZ”区域和烹饪“ABC”的帖子.