Monday, 6 February 2012

Nested if conditions in the mysql query

Many a times we need complex query based on the several if condition.
For example: you have two table and have applying condition on the basis of 3 column as you see in the sql. Actually we finding the latest updated time among all 3 column after the join of two table.
Here is the syntax for that in mysql:

SELECT u.id, u.addedon, u.updatedon, ud.modifiedon,IF( ud.modifiedon > IF( u.addedon > u.updatedon, u.addedon, u.updatedon ), ud.modifiedon,IF( u.addedon > u.updatedon, u.addedon, u.updatedon )) as maxupdate  FROM user AS u LEFT JOIN user_details AS ud ON u.id = ud.ref_id where u.id IN ($ids) ORDER BY maxupdate DESC

OR in formated way
SELECT u.id, u.addedon, u.updatedon, ud.modifiedon,
 IF( ud.modifiedon > IF( u.addedon > u.updatedon, u.addedon, u.updatedon ),
  ud.modifiedon,
  IF( u.addedon > u.updatedon, u.addedon, u.updatedon )
  ) as maxupdate
    FROM user AS u LEFT JOIN user_details AS ud ON u.id = ud.ref_id where u.id IN ($ids) ORDER BY maxupdate DESC

No comments :

Post a Comment