Shawn Shawn - 6 months ago 19
SQL Question

MySQL: create new table using same table twice

I want to use 1 table to create a new table using 2 sets of queries.

To test out the code: http://sqlfiddle.com/#!9/02e3ff/5

Reference table:



enter image description here

Desired table:



enter image description here

They share the same order_id.

type = A, updated_at = pDate

type = B, updated_at = dDate


Query 1:



select t.order_id, t.updated_at as pDate, weekday(t.updated_at) from transactions t
where t.type = 'A' group by t.order_id


Query 2:



select t.order_id, max(t.updated_at) as dDate, weekday(max(t.updated_at)) from transactions t
where t.type= 'B'
group by t.order_id;


For type = A, I want to get the earliest updated_at date, while for type = B, I want to get the latest updated_at date.

Currently, I tried union but they give me 2 rows instead of the desired table.

How do I join or union these 2 queries to get the desired table?

Alternatively, is there a better method to do this? Thanks!

Answer

You can try something like this:

SELECT order_id, min(pDate) pDate, max(dDate) dDate FROM(
    SELECT
      order_id,
      if(type='A',updated_at,null) pDate,
      if(type='B',updated_at,null) dDate
    FROM transactions
) as d
GROUP BY order_id

SQLFiddle