Optimize Sql query (Mysql )

Hi,

I have following 4 sql queries .

I want to join all these 4 queries into 1 query and get the result.

SELECT COUNT(*) AS table1_count FROM table1 WHERE active=1

SELECT COUNT(*) AS table2_count FROM table2 WHERE active=0

SELECT COUNT(*) AS table3_count FROM table3 WHERE active=1

SELECT COUNT(*) AS table4_count FROM table4 WHERE active=1

Any idea?

-Thanks

What is the table structure for each table (eg for fields to join on for example in a forum an id field in a users table and a poster_id in a posts table would be used for a join)?

SELECT 'table1' AS the_table
     , COUNT(*) AS the_count 
  FROM table1 
 WHERE active=1
UNION ALL
SELECT 'table2' 
     , COUNT(*)
  FROM table2 
 WHERE active=0
UNION ALL
SELECT 'table3' 
     , COUNT(*)
  FROM table3 
 WHERE active=1
UNION ALL
SELECT 'table4' 
     , COUNT(*) 
  FROM table4 
 WHERE active=1

:slight_smile:

Thanks r937 and SpacePhoenix

It works.

:slight_smile: