0

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
5
  • 1
    ambiguous question, ambiguous example too !! – Wajih Jan 11 '17 at 5:44
  • Please edit your question, it is not clear what are you asking for. – McNets Jan 11 '17 at 9:10
  • Found the same question but not the desired output required. Share the link @wajeeh – Ankit Saxena Jan 13 '17 at 12:22
  • I have shared th main table and desired output. See if you can help me out. I want the output exactly similar to this in dynamic way. – Ankit Saxena Jan 13 '17 at 12:24
  • where is the link ?? @AnkitSaxena – Wajih Jan 14 '17 at 5:32
0
  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'
0

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);
1
  • It is not easy to get the desired output. I am able show the output between two dates but the challenge is, when we have several changes between dates then how to show all the rows between the dates. In above example result set, 2,3,4 displaying the old values where as remaining the new values. See if you can provide me the exact solution displayed in Result. – Ankit Saxena Jan 13 '17 at 12:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.