CODEWITHSUNDEEP
mysqljoin
silkfire
silkfire

Reputation: 25965

Getting all values based on value in another table

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]

Upvotes: 3

Views: 51

Answers (3)

Lord Grosse Jeanine
Lord Grosse Jeanine

Reputation: 1131

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

Upvotes: 1

Imran
Imran

Reputation: 3072

A brick region always belongs to a region table

Assume that you have region table. So this query may be

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

OR

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

Upvotes: 0

Yazan
Yazan

Reputation: 6082

the descriptions of the question is hard :) so you want to get some rows from sales, based on date, then get all records from sales that have the same brick_region of the selected rows (by date)?

if yes, how about this? 

select * from sales where sales.brick_region IN(
select brick_region from sales WHERE 
WHERE sales.date BETWEEN '2014-01-01' AND '2014-03-30'
)

Upvotes: 0

Related Questions