mysql - 根据另一个表中的值获取所有值

Question

这应该很容易，但出于某种原因，我和我的大脑今天不想合作。

我有一个表sales，其中包含一个brick_region 列。 block 区域始终属于 区域。现在我想选择 sales 中属于我选择的特定砖 block 区域的所有值。我该怎么做？

SELECT *
FROM sales
JOIN brick_regions
ON (brick_regions.id = sales.brick_region)
WHERE sales.date BETWEEN '2014-01-01' AND '2014-03-30'
AND sales.brick_region = 2

但我希望最后一行改为:

AND brick_regions.region = [region ID of the brick region with ID 2]

Answer 1

也许尝试使用子查询？

SELECT * FROM sales 
LEFT JOIN brick_regions ON brick_regions.id = sales.brick_region 
WHERE sales.date BETWEEN '2014-01-01' AND '2014-03-30' 
AND brick_regions.region = (SELECT region FROM brick_regions WHERE id = 2);