CODEWITHSUNDEEP
mysqldistinct
Louie Miranda
Louie Miranda

Reputation: 1159

MySQL: Query distinct column with two different column dates

I have the following result from a query

SELECT DISTINCT user_id, 
                IF(ra.status = 1, created_datetime, 'No record') AS ACTIVATED, 
                IF(ra.status = 0, created_datetime, 'No record') AS DE_ACTIVATED 
FROM   accounts ra 
WHERE  user_id IN ( 12345678 ); 

+----------+---------------------+---------------------+
| user_id  | ACTIVATED           | DE_ACTIVATED        |
+----------+---------------------+---------------------+
| 12345678 | No record           | 2013-04-28 02:32:36 |
| 12345678 | 2013-03-28 02:32:36 | No record           |
+----------+---------------------+---------------------+

I am trying to have only 1 row of record, combining the activated and de-activated. Basically, making it like this.

+----------+---------------------+---------------------+
| user_id  | ACTIVATED           | DE_ACTIVATED        |
+----------+---------------------+---------------------+
| 12345678 | 2013-03-28 02:32:36 | 2013-04-28 02:32:36 |
+----------+---------------------+---------------------+

Any help is much appreciated as I build my query ninja skills.

Regards

Upvotes: 0

Views: 48

Answers (1)

Ed Gibbs
Ed Gibbs

Reputation: 26343

Take the MAX of both conditions, but make the "false" condition evaluate to null. Then wrap it in COALESCE so if the calcualted MAX is null it'll be replaced with no record:

SELECT user_id,
  COALESCE(MAX(IF(ra.status = 1, created_datetime, null)), 'No record') AS Activated,
  COALESCE(MAX(IF(ra.status = 0, created_datetime, null)), 'No record') AS Deactivated
FROM accounts ra
WHERE user_id IN (12345678)

And if you'll be querying more than one user, add this to the end:

GROUP BY user_id

Upvotes: 1

Related Questions