Sasha Pachev wrote:
Gustavo Andrade wrote:
I want to know if its possible to count the total records of multiple
tables:
Example: I have 3 tables. I want to know the total records of each table
using only 1 query.
Is that possible?
It is actually possible with 4.0, although rather convoluted:
(select count(*) from f) union (select count(*) from f1) union (select count(*) from ft1);
Unfortunately, the following does not work quite right:
(select count(*),'f' as table_name from f) union (select count(*),'f1' as table_name from f1) union (select count(*),'ft1' as table_name from ft1);
+----------+------------+ | count(*) | table_name | +----------+------------+ | 3 | f | | 6 | f | | 7 | f | +----------+------------+ 3 rows in set (0.00 sec)
However, if you pad the names of tables which are shorter to the length of the longest one:
mysql> (select count(*),'f ' as table_name from f) union (select count(*),'f1 ' as table_name from f1) union (select count(*),'ft1' as table_name from ft1);
+----------+------------+
| count(*) | table_name |
+----------+------------+
| 3 | f |
| 6 | f1 |
| 7 | ft1 |
+----------+------------+
3 rows in set (0.00 sec)
Once the bug gets fixed, you would not need to pad.
As I understand it, the length of the field is based on the first SELECT, so you only need to pad the first one (or put the longest one first).
mysql> (SELECT COUNT(*),'t ' AS table_name FROM t)
-> UNION
-> (SELECT COUNT(*),'t1' AS table_name FROM t1)
-> UNION
-> (SELECT COUNT(*),'table1' AS table_name FROM table1);
+----------+------------+
| COUNT(*) | table_name |
+----------+------------+
| 14 | t |
| 7 | t1 |
| 4 | table1 |
+----------+------------+
3 rows in set (0.00 sec)mysql> (SELECT COUNT(*),'table1' AS table_name FROM table1)
-> UNION
-> (SELECT COUNT(*),'t' AS table_name FROM t)
-> UNION
-> (SELECT COUNT(*),'t1' AS table_name FROM t1);
+----------+------------+
| COUNT(*) | table_name |
+----------+------------+
| 4 | table1 |
| 14 | t |
| 7 | t1 |
+----------+------------+
3 rows in set (0.00 sec)Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
