Multiple select statements in a single query

Question

I am creating a report in php (mysql),

Example:

`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` 

I have 12 tables.

Can I do this in one request. If I did? Is the process slowing down?

Answer 1

 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 2

If you use MyISAM tables, the fastest way to directly query statistics is:

 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 need to request the count () count because the reported value in the schema information file is incorrect.

Answer 3

You can certainly choose a Select Agregation as postulated by Ben James, however this will result in a view with as many columns as you have tables. An alternative 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 good thing about this statement is that you can explicitly write Union statements and generate a view or create a temporary table to store values ​​that are added sequentially from the Proc calendar using variables instead of table names. I am more inclined to the latter, but it depends on personal preferences and application. If you are sure that the tables will never change, you need data in a single row format and you will not add tables. stick to Ben James decision. Otherwise, I would recommend flexibility, you can always hack the structure of cross-tabs.

Answer 4

 select RTRIM(A.FIELD) from SCHEMA.TABLE A where RTRIM(A.FIELD) = ('10544175A') UNION select RTRIM(A.FIELD) from SCHEMA.TABLE A where RTRIM(A.FIELD) = ('10328189B') UNION select RTRIM(A.FIELD) from SCHEMA.TABLE A where RTRIM(A.FIELD) = ('103498732H') 

Answer 5

I know this is an old stack, but I will post this case of selecting multiple SQL

  SELECT bp.bizid, bp.usrid, bp.website, ROUND((SELECT SUM(rating) FROM ratings WHERE bizid=bp.bizid)/(SELECT COUNT(*) FROM ratings WHERE bizid=bp.bizid), 1) AS 'ratings', (SELECT COUNT(*) FROM bzreviews WHERE bizid=bp.bizid) AS 'ttlreviews', bp.phoneno, als.bizname, (SELECT COUNT(*) FROM endorsment WHERE bizid=bp.bizid) AS 'endorses' , als.imgname, bp.'location', bp.'ownership', (SELECT COUNT(*) FROM follows WHERE bizid=bp.bizid) AS 'followers', bp.categories, bp.openhours, bp.bizdecri FROM bizprofile AS bp INNER JOIN alluser AS als ON bp.usrid=als.userid WHERE als.usertype='Business' 

Answer 6

 SELECT t1.credit, t2.debit FROM (SELECT Sum(c.total_amount) AS credit FROM credit c WHERE c.status = "a") AS t1, (SELECT Sum(d.total_amount) AS debit FROM debit d WHERE d.status = "a") AS t2