At 21:17 -0600 5/17/04, 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.
It's fixed in 4.1.1:
http://dev.mysql.com/doc/mysql/en/UNION.html
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
