Forums

I hate to ask for such specific help but I am really stuck here and I don't really want to cut corners buy doing huge iterations over my database tables.

I have the following tables: Table name 'Prices'

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | mediumint(9) | NO   | PRI | NULL    | auto_increment |
| product_id | int(11)      | YES  |     | NULL    |                |
| price      | text         | YES  |     | NULL    |                |
| date       | text         | YES  |     | NULL    |                |
| time       | text         | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

And Table name 'Products'

+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | mediumint(9) | NO   | PRI | NULL    | auto_increment |
| category_id     | int(11)      | YES  |     | NULL    |                |
| product_urls    | text         | YES  |     | NULL    |                |
| product_title   | text         | YES  |     | NULL    |                |
| product_image   | text         | YES  |     | NULL    |                |
| product_content | text         | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+

The connection here is the id in the 'Products' table and the products_id column in the 'Prices' column. Essentially, I have a script running that grabs the URLs and goes off and scrapes the prices and then updates the database.

The 'Prices' table updates every hour.

I'd like to display the following, ideally with one database query....

• products.product_title
• products.product_image
• prices.price (current price i.e. the very last insert for each product id)
• prices.price(the percentage difference between today's average price and yesterdays average price)
• prices.price (the percentage difference between the current months average price and the previous months average price)

I can do all these things separately, but bringing it together into one call I can not.

Just for clarity, I am wanting to grab all of the products at once - I have been doing this with:

...group by product_id

If someone could hook me up with the correct code I will be eternally grateful (I may have some follow up questions so I full understand what you have done).

Thank you in advance :)

Are you writing your SQL code directly? Or using an ORM like Django's, or SQLAlchemy?

Ah sorry, direct, using mysqldb.

Hmm, sorry, this is trickier than I thought it was -- I'd not read your spec properly, specifically the particular averages you want.

I reckon this is probably a good question for Stack Overflow, either the main site or the DBA one. There's bound to be someone there who'll know straight off exactly how to do what you need. I'd love to know what they come up with, though! It's a really interesting question :-)

Hey Giles,

That actually makes me feel a little better :)

I done as you suggest you can see it here http://stackoverflow.com/questions/30254650/mysql-multiple-select-statements-with-different-conditions

Thanks again.