Shawn Shawn - 4 months ago 8
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:!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!


You can try something like this:

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