SQL Query Self Join

Question

How to get the below result set from mysql query. The challenge is everything is just in single table and only way to differentiate one row with another is Date field.

product_id     |   CREATED_STAMP     | PRODUCT_PRICE_PURPOSE_ID | PRICE   | SERIAL_NUMBER

10000000349098 | 2017-01-04 13:11:07 |  Purchase_Price          |   30    |  ytre
10000000349098 | 2017-01-04 13:11:07 |  MRP                     |   50    |  ytre
10000000349098 | 2017-01-04 13:09:43 |  Purchase_Price          |   100   |  gfr
10000000349098 | 2017-01-04 13:09:43 |  MRP                     |   280   |  gfr

Result

 Product Id    |  SN Purchase | Price | MRP | Serial Number | Purchase Price    | MRP

10000000349098 |  gfr         |  100  | 280 | ytre          |  30               | 50

Answer 1

1. Create a View to retrieve the main record:

   CREATE 
     ALGORITHM = UNDEFINED 
     SQL SECURITY DEFINER
   VIEW `new_view` AS
   SELECT 
       t.*
   FROM tablename t
   WHERE t.PRODUCT_PRICE_PURPOSE_ID = 'Purchase_Price')
   ORDER BY t.CREATED_STAMP
   LIMIT 1
   

2. Build the query:

   SELECT
      t.product_id, t.SERIAL_NUMBER, t.PRICE,
      t_2.PRICE,
      t_3.SERIAL_NUMBER, t_3.PRICE,
      t_4.PRICE
   FROM new_view t
     LEFT JOIN tableName t_2 ON t.CREATED_STAMP = t_2.CREATED_STAMP and t.SERIAL_NUMBER = t_2.SERIAL_NUMBER and t_2.PRODUCT_PRICE_PURPOSE_ID = 'MRP'
     LEFT JOIN tableName t_3 ON t.product_id = t_3.product_id and t_3.CREATED_STAMP > t.CREATED_STAMP and t_3.PRODUCT_PRICE_PURPOSE_ID = 'Purchase_Price'
     LEFT JOIN tableName t_4 ON t_3.CREATED_STAMP = t_4.CREATED_STAMP and t_4.SERIAL_NUMBER = t_3.SERIAL_NUMBER and t_4.PRODUCT_PRICE_PURPOSE_ID = 'MRP'
   where t.PRODUCT_PRICE_PURPOSE_ID = 'Purchase_Price'
   

---

OR in one query

SELECT
   t.product_id, t.SERIAL_NUMBER, t.PRICE,
   t_2.PRICE,
   t_3.SERIAL_NUMBER, t_3.PRICE,
   t_4.PRICE
FROM
 (SELECT * FROM tablename WHERE PRODUCT_PRICE_PURPOSE_ID = 'Purchase_Price'
  ORDER BY CREATED_STAMP LIMIT 1
 ) as t
   LEFT JOIN tableName t_2 ON t.CREATED_STAMP = t_2.CREATED_STAMP and t.SERIAL_NUMBER = t_2.SERIAL_NUMBER and t_2.PRODUCT_PRICE_PURPOSE_ID = 'MRP'
   LEFT JOIN tableName t_3 ON t.product_id = t_3.product_id and t_3.CREATED_STAMP > t.CREATED_STAMP and t_3.PRODUCT_PRICE_PURPOSE_ID = 'Purchase_Price'
   LEFT JOIN tableName t_4 ON t_3.CREATED_STAMP = t_4.CREATED_STAMP and t_4.SERIAL_NUMBER = t_3.SERIAL_NUMBER and t_4.PRODUCT_PRICE_PURPOSE_ID = 'MRP'
WHERE t.PRODUCT_PRICE_PURPOSE_ID = 'Purchase_Price'

Answer 2

if product_id is your primary key then you can use LEFT JOIN to join this two tables. conciser first_table name as t1 and second table name as t2.

SELECT t1.product_id,t1.CREATED_STAMP,t1.PRODUCT_PRICE_PURPOSE_ID,t1.PRICE,t1.SERIAL_NUMBER,t2.SN Purchase,t2.MRP,t2.Purchase Price 
FROM t1
LEFT JOIN t2 ON (t1.product_id = t2.product_id);