Join query result with itself in MySQL

Question

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?

can you show us a part of products table? imho you do not need a join here — Zahiro Mor, Apr 19 '16 at 09:05
products table is simple: it holds product id, a price for a specific day and the serial number of that day. — Max Segal, Apr 19 '16 at 09:07
then you can use pivot on dates...... check http://stackoverflow.com/questions/10428993/understanding-pivot-function-in-t-sql out.... — Zahiro Mor, Apr 19 '16 at 09:12

score 2 · Accepted Answer · edited Apr 19 '16 at 11:06

2

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

edited Apr 19 '16 at 11:06

Max Segal

1,955
1
24
53

answered Apr 19 '16 at 09:27

kostas

461
5
13

score 1 · Answer 2 · edited May 23 '17 at 12:07

You could do a bunch of things, just a few options could be:

Just let mysql handle the optimization
- this will likely work fine until you hit many rows
Make a view for your base query and use that
- could increase performance but mostly increases readability (if done right)
Use a table (non temporary) and insert your initial rows in there. (unfortunately you cannot refer to a temporary table more than once in a query)
- this will likely be more expensive performance wise until a certain number of rows is reached.

Depending on how important performance is for your situation and how many rows you need to work with the "best" choice would change.

score -1 · Answer 3 · answered Apr 19 '16 at 09:12

-1

Just to get duplicates in the same row?

select product_id as id1, price as price1, price_day as priceday1, product_id as id2, price as price2, price_day as priceday2,
   from products
     where price>10

answered Apr 19 '16 at 09:12

user3791775

426
3
5

That's not what I asked. I want to get the price for the PREVIOUS day in the same row. – Max Segal Apr 19 '16 at 09:16
Your select statements are exactly the same: select product_id, price, price_day and are selecting from the same table – user3791775 Apr 19 '16 at 09:24
Look on my conditions of the join – Max Segal Apr 19 '16 at 09:25
So product_id is not unique? I don't understand your table.. where is this previous day value stored? – user3791775 Apr 19 '16 at 09:28
(product_id, price, price_day) is the primary key of the table – Max Segal Apr 19 '16 at 10:01

Join query result with itself in MySQL

3 Answers3