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