0

I've been looking around and trying to get this to work but I can't seem to get it. I have 2 tables:

TABLE: products

| id | name      | some more values |
|----|-----------|------------------|
| 1  | Product 1 | Value 1          |
| 2  | Product 2 | Value 2          |
| 3  | Product 3 | Value 3          |


TABLE: value

| pid | value     | stamp            |
|-----|-----------|------------------|
| 1   | 7         | 2015-07-11       |
| 2   | 4         | 2015-07-11       |
| 3   | 8         | 2015-07-11       |
| 1   | 9         | 2015-07-21       |
| 2   | 4         | 2015-07-21       |
| 3   | 6         | 2015-07-21       |

First table simply has a list of products, second table has a value for each product (by pid), and the timestamp the value. note: timestamps are not every day, nor are they evenly spaced.

What I would like, is a resulting table like this:

| id | name      | some more values | value now | value last month |
|----|-----------|------------------|-----------|------------------|
| 1  | Product 1 | Value 1          | 9         | 7                |
| 2  | Product 2 | Value 2          | 4         | 4                |
| 3  | Product 3 | Value 3          | 6         | 8                |

where 'value now' is the value of the newest timestamp, and the 'value last month' is the value of the timestamp closest to the newest timetamp - 30 days. Keep in mind that -30 days might not have a specific timestamp, the query will need to find the closest timestamp. (looking only up or down doesn't matter, it's an approximation.)

I have made some huge queries but I'm pretty sure there must be an easier way... Any help would be appreciated.

David Halford
  • 125
  • 1
  • 6

1 Answers1

0

Assuming you get last month and year by PHP or by mysql function, here is a not checked query I hope it will work on first time: 

SELECT *, v_now, v_lastmonth FROM products p 
LEFT JOIN (SELECT `value` AS v_now FROM value ORDER BY stamp DESC) AS v_now ON p.id=v_now.pid
LEFT JOIN (SELECT `value` AS v_lastmonth FROM value 
WHERE month(stamp)='$month' AND year(stamp)='$year'
ORDER BY stamp DESC) AS v_now ON p.id=v_now.pid

You can use group by to get one row for each product result.

Tariq
  • 2,853
  • 3
  • 22
  • 29
  • The getting the last month or year in MySQL is the part I'm having trouble with. Could you elaborate? – David Halford Jul 28 '15 at 06:33
  • I am not following you, but I will try this: Using mysql functions like date and time may complicate your syntax and lower code performance. You can benefit the PHP functions to get last month and year, and use that in the query, instead of using mysql functions like 'intervals'. here is another issue like yours: http://stackoverflow.com/questions/6845604/mysql-date-add-month-interval – Tariq Jul 28 '15 at 09:41