'mySQL - comparing values to other records in the table

I have a mySQL database that takes the below form. It displays price information by product and day for a number of competitor sites (comp_site). I am trying to get the difference between the competitors price and the site price for the days were the information exists.

enter image description here

The required output would look like the below:

required output

Any help would be greatly appreciated.

mysql


Solution 1:[1]

You can join a table with itself, just give an alias to be able to distinguish between the two copies of the table :

SELECT p1.comp_site, p1.product, p1.price, ..., (p1.price - p2.price) as difference
  FROM products p1
   JOIN products p2 ON p1.product = p2.product AND p1.date = p2.date 
    -- not sure I understood your requirement for the condition of the JOIN
  WHERE p2.comp_site = 398
  ORDER BY ....

(you can also LEFT JOIN or RIGHT JOIN or ...)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 LeGEC

Related Questions