Mysql Query optimisation

Question

Hi need some help with a mysql query

my data is like so

Table base_status

| id | order_id | status | dt_added |
-------------------------------------
| 1  | 123      | xyz    | date3    |
| 2  | 123      | abc    | date2    |
| 3  | 123      | pqr    | date1    |

I'm trying to get output as such

| order_id | xyz_date | abc_date | pqr_date |
-------------------------------------------
| 123      | date3    | date2    | date3    |

MySQL query:

select BS.order_id, XYZ.dt_added AS xyz_date, 
       ABC.dt_added AS abc_date, PQR.dt_added AS pqr_date,
from base_status BS
LEFT JOIN base_status XYZ ON BS.order_id=TAS.order_id and TAS.status='xyz'
LEFT JOIN base_status ABC ON BS.order_id=TAS.order_id and TAS.status='abc'
LEFT JOIN base_status PQR ON BS.order_id=PQR.order_id and PQR.status='pqr'
....

My question: How can I avoid the 3 left joins. Is there anything to do same thing with only a single join?

roberto06 · Accepted Answer · 2016-11-23 09:03:30Z

1

The best way not to query the same table multiple times is to use GROUP_CONCAT to group the three dates, and FIELD to keep the entry order :

SELECT order_id, GROUP_CONCAT(dt_added) AS "xyz_date,abc_date,pqr_date "
FROM base_status
WHERE status IN ('xyz', 'abc', 'pqr')
GROUP BY order_id
ORDER BY FIELD(status, 'xyz', 'abc', 'pqr')

Granted, the result will be slightly different from what you're trying to achieve :

| order_id | xyz_date,abc_date,pqr_date |
-----------------------------------------
| 123      | date3,date2,date1          |

answered Nov 23, 2016 at 9:03

roberto06

3,8641 gold badge20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marcus · Accepted Answer · 2016-11-23 09:04:03Z

1

You can use a group_concat (http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat)

but this solution need post-processing to split the values

SELECT order_id, GROUP_CONCAT(status), GROUP_CONCAT(dt_added) FROM     base_status GROUP BY order_id

the Cells will look like pqr, abc, xyz and date1, date2, date2

Then you could use SUBSTRING_INDEX to split it up again

answered Nov 23, 2016 at 9:04

Marcus

1,9402 gold badges18 silver badges28 bronze badges

Comments

Wax Cage · Accepted Answer · 2016-11-23 09:01:24Z

0

Try this

SELECT 
    order_id, GROUP_CONCAT(status), GROUP_CONCAT(dt_added)
FROM
    base_status
GROUP BY
    order_id

To get output like this

| order_id | status        | dt_added              |
----------------------------------------------------
| 123      | xyz, abc, pqr | date3, date2, date1   |

answered Nov 23, 2016 at 9:01

Wax Cage

8062 gold badges10 silver badges25 bronze badges

Comments

Mihai · Accepted Answer · 2016-11-23 09:10:18Z

0

In case you have more than 3 dates,dynamic pivot is the way to go

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(case when dt_added = ''',
      dt_added ,
      ''' then dt_added  end) AS ',
      CONCAT(status,'_','date')
    )
  ) INTO @sql
from base_status;

SET @sql = CONCAT('SELECT order_id, ', @sql, ' from base_status
group by order_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

FIDDLE

answered Nov 23, 2016 at 9:10

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

Comments

Kickstart · Accepted Answer · 2016-11-24 16:05:19Z

0

You can use an aggregrate function combined with the IF statement to bring back the columns as you specify:-

SELECT order_id,
        MAX(IF(status = 'xyz', dt_added, NULL)) AS xyz_date,
        MAX(IF(status = 'abc', dt_added, NULL)) AS abc_date,
        MAX(IF(status = 'pqr', dt_added, NULL)) AS pqr_date
FROM base_status 
GROUP BY order_id

However I suspect that if properly indexed the 3 table join should perform fine.

answered Nov 24, 2016 at 16:05

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

Collectives™ on Stack Overflow

Mysql Query optimisation

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related