Basic Mysql Query : label 1

Mysql comma count from field value :
Count string separators from a MySQL query Ex: 1,2,3,4,5 as the string is comma separated so the separator count will be 4.

id  dish_id  name
1   2        a,b,c,d
2   1        b
3   3        c
4   2        d

Query

SELECT column_name,(CHAR_LENGTH(column_name) - CHAR_LENGTH(REPLACE(column_name, ',', '')) + 1) as cnt 
FROM table_name

Result

id  cnt
1    4
2    1
3    1
4    1

---

Multiple row as comma separated single row Table

id  dish_id  name
1    2        a
2    1        b
3    3        c
4    2        d

Query

SELECT dish_id, GROUP_CONCAT(id) FROM tablename GROUP BY dish_id Result

Result

dish_id  GROUP_CONCAT(id)
1         2
2         1,4
3         3

---

Fetch all records whose first letter is capital in column :

select email from table_name where ASCII(left(email, 1)) between ASCII('A') and ASCII('Z')

---

Update new recods with first letter in capital :

UPDATE table_name
SET newemail=CONCAT(LCASE(LEFT(email, 1)),SUBSTRING(email, 2))
WHERE ASCII(left(email, 1)) between ASCII('A') and ASCII('Z')

---

N table JOIN syntax in SQL

SELECT t1.col, t3.col FROM table1 join table2 ON table1.primarykey = table2.foreignkey
join table3 ON table2.primarykey = table3.foreignkey

We first join table 1 and table 2 which produce a temporary table with combined data from table1 and table2, which is then joined to table3. This concept can be extended more then 3 tables if there are n table then there is n-1 join statement.In above query there is 3 table then there is 2 join statement table1.primarykey = table2.foreignkey and table2.primarykey = table3.foreignkey.

---

If Condition in Mysql Query :

SELECT col_name, ( IF(status='0',col_name1,col_name2) ) AS allies_name FROM tbl_name

SELECT col_name, ( IF(status='0','Inactive','Active') ) AS allies_name FROM tbl_name

In above query if a condition is true then col_name1 is returned otherwise col_name2 is returned.

---

CASE Statment in Mysql Query :

SELECT
  T1.*,
  CASE
    WHEN T1.status = '0' THEN 'Inactive'
    WHEN T1.status = '1' THEN 'Active' 
    WHEN T1.status = '2' THEN 'Suspend' 
    ELSE 'Something' 
    END
FROM tbl_name T1

---