Mysql Query tips & tricks

Few Mysql queries that will help you develop websites and resolve complex codes.

Here are few Mysql Query tips:

Finding 2nd highest salary in employee table

 SELECT MAX(`salary`) from tbl where `salary` < (select MAX(`salary`) from tbl);

Mysql order by FIELD

 SELECT * FROM tbl ORDER BY FIELD(first_name,'John') desc, FIELD(last_name,’Doe′) ASC;

Show category-parent category

 SELECT
 T2.category_name AS subcategory,
 T1.category_name AS category
 FROM category T1
 JOIN category T2
 ON T1.category_id = T2.category_pid

Show category-parent category with parent

 SELECT T2.category_name AS subcategory, T1.category_name AS category
 FROM category T1
 RIGHT JOIN category T2 ON T1.category_id = T2.category_pid

Show null replace with text

 SELECT
 T2.category_name AS subcategory,
 IFNULL(T1.category_name, “replacewith”) AS category
 FROM category T1
 RIGHT JOIN category T2
 ON T1.category_id=T2.category_pid IFNULL(T1.category_name, “replacewith”)

Show multilevel at single query

 SELECT * FROM
 (
 SELECT 0 AS parent_id,” AS parent,cat_id AS category_id,category AS category FROM categories WHERE parent_cat_id=0
 UNION
 SELECT cat.cat_id AS parent_id,cat.category AS parent,chd.cat_id AS category_id,chd.category AS category FROM categories chd
 INNER JOIN categories cat ON cat.cat_id=chd.parent_cat_id
 ) AS t
 ORDER BY concat(t.parent,t.category)