CODEWITHSUNDEEP
mysql
Crazyshezy
Crazyshezy

Reputation: 1620

Mysql Query optimisation

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?

Upvotes: 0

Views: 76

Answers (5)

Kickstart
Kickstart

Reputation: 21513

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.

Upvotes: 0

Mihai
Mihai

Reputation: 26784

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

Upvotes: 0

Marcus
Marcus

Reputation: 1930

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

Upvotes: 1

roberto06
roberto06

Reputation: 3864

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          |

Upvotes: 1

Wax Cage
Wax Cage

Reputation: 788

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   |

Upvotes: 0

Related Questions