Multiple select statements in Single query

Question

I am generating a report in php (mysql),

ex:

`select count(id) as tot_user from user_table
 select count(id) as tot_cat from cat_table
 select count(id) as tot_course from course_table`

Like this I have 12 tables.

Can i make it in single query. If i did? Process gets slow?

Answer 1

I'm pretty sure you can't select data from multiple table in MySQL. Especially as I've never seen the "AS" keyword.

Answer 2

You can certainly us the a Select Agregation statement as Postulated by Ben James, However This will result in a view with as many columns as you have tables. An alternate method may be as follows:

SELECT COUNT(user_table.id) AS TableCount,'user_table' AS TableSource FROM user_table UNION SELECT COUNT(cat_table.id) AS TableCount,'cat_table' AS TableSource FROM cat_table UNION SELECT COUNT(course_table.id) AS TableCount, 'course_table' AS TableSource From course_table;

The Nice thing about an approch like this is that you can explicitly write the Union statements and generate a view or create a temp table to hold values that are added consecutively from a Proc cals using variables in place of your table names. I tend to go more with the latter, but it really depends on personal preference and application. If you are sure the tables will never change, you want the data in a single row format, and you will not be adding tables. stick with Ben James' solution. Otherwise I'd advise flexibility, you can always hack a cross tab struc.

Answer 3

SELECT  (
    SELECT COUNT(*)
    FROM   user_table
    ) AS tot_user,
    (
    SELECT COUNT(*)
    FROM   cat_table
    ) AS tot_cat,
    (
    SELECT COUNT(*)
    FROM   course_table
    ) AS tot_course

Answer 4

The 'AS" Keyword is the alias command. It can be highly useful to return a consistent agregate table/field name from a non-uniform structure (as above), but it has it's problems.

http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html

Answer 5

If you use MyISAM tables, the fastest way is querying directly the stats:

select table_name, table_rows 
     from information_schema.tables 
where 
     table_schema='databasename' and 
     table_name in ('user_table','cat_table','course_table')

If you have InnoDB you have to query with count() as the reported value in information_schema.tables is wrong.