CODEWITHSUNDEEP
mysqlsql
Ayoub.A
Ayoub.A

Reputation: 2093

MySql: Where for each id

I have a table of prices defined like this:

Prices

amount
item_id
effective_date
end_date

item_id is not unique, so I have a list of items and I want to get their corresponding amount according to their effective_date and end_date, I don't want a query for each item, so I thought of doing this:

SELECT amount WHERE item_id IN (//set of ids) WHERE ?

so how can I specify a date condition for every id? and is it even possible?

EDIT

Here is a sample code 

amount    item_is    effective_date    end_date
125.00    5          12/12/2015        01/03/2016
60.00     5          03/05/2015        12/12/2015
20.00     1          04/06/2015        01/09/2016
100.00    4          01/12/2015        01/03/2017
40.00     4          12/12/2014        01/03/2015
130.00    1          01/11/2010        01/06/2012

Say I want the first item 4 and the last item 1, in one single query, how can accomplish this

Upvotes: 0

Views: 772

Answers (2)

McNets
McNets

Reputation: 10807

Use AND to add more conditions

SELECT amount WHERE item_id IN (//set of ids) AND efective_date = SomeDate

If there are different conditions don't use IN

SELECT amount
FROM YourTable
WHERE (item_iS = 4 AND efective_date = CAST('01/12/2015' AS DATE)
      AND / OR
      (item_iS = 1 AND efective_date = CAST('01/11/2010' AS DATE)

Upvotes: 1

stk
stk

Reputation: 114

Do you have a different date range for each item ? If so you cannot use an IN clause on items, a separate query needs to written for each item and range.

If you just need the latest or all price variation in a given date range, you could do this

SELECT item_id, effective_date, end_date, amount WHERE item_id IN (//set of ids) AND effective_date <= adate and end_date >= samedate.

Upvotes: 0

Related Questions