Max Segal Max Segal - 7 months ago 9
SQL Question

Join query result with itself in MySQL

Let's say I have a query:

select product_id, price, price_day
from products
where price>10


and I want to join the result of this query with itself (if for example I want to get in the same row product's price and the price in previous day)

I can do this:

select * from
(
select product_id, price, price_day
from products
where price>10
) as r1
join
(
select product_id, price, price_day
from products
where price>10
) as r2
on r1.product_id=r2.product_id and r1.price_day=r2.price_day-1


but as you can see I am copying the original query, naming it a different name just to join its result with itself.

Another option is to create a temp table but then I have to remember to remove it.

Is there a more elegant way to join the result of a query with itself?

Answer

self join query will help

    select a.product_ID,a.price
          ,a.price_day
          ,b.price as prevdayprice
          ,b.price_day as prevday 
    from Table1 a 
    inner join table1 b 
    on a.product_ID=b.product_ID  and  a.price_day = b.price_day+1   
    where a.price  >10