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.
-- Sasha Pachev Create online surveys at http://www.surveyz.com/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
