CODEWITHSUNDEEP
mysqlsqlmysql-python
Adders
Adders

Reputation: 665

Multiple SELECT statements with different conditions in one query

I have the following tables:

'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    |                |
+------------+--------------+------+-----+---------+----------------+

'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 between Products.id and Prices.products_id.

I have a script running that grabs the URLs, scrapes prices from particular web pages and then updates the 'Prices' table every hour.

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

I can do these things separately, but I cannot bring it together into one query.

Just for clarity, I am wanting to grab all of the products at once within a particular category - with that in mind I have been doing this:

...WHERE category_id=%s...GROUP BY product_id...

I am writing my SQL directly, specifically using the MySQLdb Python library.

Upvotes: 1

Views: 1311

Answers (2)

Adders
Adders

Reputation: 665

Final snippet as per @Cosmin's suggestions...

SELECT p.*,
     (SELECT prices.price
          FROM prices
          WHERE prices.product_id = p.id
          ORDER BY prices.id DESC
          LIMIT 1) as last_price,

     (SELECT ROUND(AVG(prices.price), 2)
          FROM prices
          WHERE prices.product_id = p.id AND date = CURRENT_DATE()
          ORDER BY prices.id DESC
          LIMIT 1) as todays_average_price,

     (SELECT ROUND(AVG(prices.price), 2)
          FROM prices
          WHERE prices.product_id = p.id AND date = SUBDATE(CURRENT_DATE, 1)
          ORDER BY prices.id DESC
          LIMIT 1) as yesterdays_average_price,

     (SELECT ROUND(AVG(prices.price), 2)
          FROM prices
          WHERE prices.product_id=p.id AND MONTH(date)=MONTH(current_date)
          ORDER BY prices.id DESC
          LIMIT 1) as current_months_average_price,

     (SELECT ROUND(AVG(prices.price), 2)
          FROM prices
          WHERE prices.product_id=p.id AND MONTH(date)=MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
          ORDER BY prices.id DESC
          LIMIT 1) as previous_months_average_price
FROM products p
WHERE p.category_id=%s;

Wasn't 100% sure on how to do the calculation of percentage difference within the query but I will just do that with Python to keep the query clean.

Upvotes: 0

Cosmin
Cosmin

Reputation: 1490

Untested example for the first subselect (make the other subselects on this template) :

SELECT p.*,
     (SELECT prices.price
          FROM prices
          WHERE prices.product_id = p.id
          ORDER BY prices.id DESC
          LIMIT 1) as last_price
FROM product p
WHERE p.category_id = 4;

Probably not the most efficient way tough...

Upvotes: 1

Related Questions