CODEWITHSUNDEEP
mysql
chchrist
chchrist

Reputation: 19802

Minimum and max price between price range mysql query

I have this table "prices"

id | price_per_week | date_start | date_end
1  | 500            | timestamp  | timestamp
1  | 5000           | timestamp  | timestamp
2  | 100            | timestamp  | timestamp
3  | 300            | timestamp  | timestamp
3  | 200            | timestamp  | timestamp
3  | 6000           | timestamp  | timestamp
3  | 50             | timestamp  | timestamp
4  | 600            | timestamp  | timestamp
5  | 800            | timestamp  | timestamp
5  | 200            | timestamp  | timestamp

I want to get all the ids where their min and max value are between a price range. for example all the ids that have min price_per_week >= 200 and max price_per_week 1000.

When I run this query

SELECT id FROM prices WHERE price_per_week BETWEEN 200 AND 1000

it returns also the id 1,3 which have a max price_per_week greater than 1000

I think I should use subqueries somehow, but I am still learning...

Upvotes: 0

Views: 9532

Answers (2)

slaakso
slaakso

Reputation: 9050

If you only want id's whose weekly price is always inside the range, try think like this:

"Show me id's that do not belong to set that have price out of range"

SELECT id 
FROM prices 
WHERE id NOT IN (
  SELECT id
  FROM prices
  WHERE price_per_week not between 200 and 1000
);

Upvotes: 2

odaa
odaa

Reputation: 261

you have multiple rows with the same id, so this could be the problem. your query select every row with price_per_week between 200 and 1000.

Upvotes: 1

Related Questions