Getting all values based on value in another table

Question

This should be really easy but for some reason me and my brain don't want to cooperate today.

I've got a table sales that contains a brick_region column. A brick region always belongs to a region. Now I want to select all values in sales that belong to the region of the specific brick region that I've selected. How do I do this?

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

But I want the last line to be this instead:

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

Answer 1

Maybe try with a sub query?

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);