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)