COUNT(*) from multiple tables in MySQL

You can do it by using subqueries, one subquery for each tableCount : SELECT (SELECT COUNT(*) FROM table1 WHERE someCondition) as table1Count, (SELECT COUNT(*) FROM table2 WHERE someCondition) as table2Count, (SELECT COUNT(*) FROM table3 WHERE someCondition) as table3Count

SELECT one column if the other is null

The ANSI means is to use COALESCE: SELECT COALESCE(, AS `date` … The MySQL native syntax is IFNULL: SELECT IFNULL(, AS `date` … Unlike COALESCE, IFNULL is not portable to other databases. Another ANSI syntax, the CASE expression, is an option: SELECT CASE WHEN IS NULL THEN ELSE END AS …

Read more

Illegal mix of collations error in MySql

Here’s how to check which columns are the wrong collation: SELECT table_schema, table_name, column_name, character_set_name, collation_name FROM information_schema.columns WHERE collation_name=”latin1_general_ci” ORDER BY table_schema, table_name,ordinal_position; And here’s the query to fix it: ALTER TABLE tbl_name CONVERT TO CHARACTER SET latin1 COLLATE ‘latin1_swedish_ci’; Link

GROUP_CONCAT with limit

One somewhat hacky way to do it is to post-process the result of GROUP_CONCAT: substring_index(group_concat(s.title SEPARATOR ‘,’), ‘,’, 3) as skills Of course this assumes that your skill names don’t contain commas and that their amount is reasonably small. fiddle A feature request for GROUP_CONCAT to support an explicit LIMIT clause is unfortunately still not …

Read more