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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I know this is an old stack but i will post this Multi-SQL select case      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

User · Answer

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

[mysql] Multiple select statements in Single query

Examples related to mysql