Is it possible to report on 2 tables without using a subquery?

Question

You have one table against which you wish to count the number of items in two different tables. In this example I used buildings, men and women

DROP TABLE IF EXISTS building;
DROP TABLE IF EXISTS men;
DROP TABLE IF EXISTS women;
CREATE TABLE building(name VARCHAR(255));
CREATE TABLE men(building VARCHAR(255), name VARCHAR(255));
CREATE TABLE women(building VARCHAR(255), name VARCHAR(255));
INSERT INTO building VALUES('building1');
INSERT INTO building VALUES('building2');
INSERT INTO building VALUES('building3');
INSERT INTO men VALUES('building1', 'andy');
INSERT INTO men VALUES('building1', 'barry');
INSERT INTO men VALUES('building2', 'calvin');
INSERT INTO men VALUES(null, 'dwain');
INSERT INTO women VALUES('building1', 'alice');
INSERT INTO women VALUES('building1', 'betty');
INSERT INTO women VALUES(null, 'casandra');

select 
   r1.building_name, 
   r1.men,
   GROUP_CONCAT(women.name) as women,
   COUNT(women.name) +  r1.men_count as count
from 
   (select 
      building.name as building_name,
      GROUP_CONCAT(men.name) as men,
      COUNT(men.name) as men_count 
   from 
      building 
   left join 
      men on building.name=men.building
   GROUP BY building.name) as r1
left join 
   women on r1.building_name=women.building 
GROUP BY r1.building_name;

Might there be another way? The problem with the above approach is that the columns of the two tables in the subquery are hidden and need to be redeclared in the outer query. Doing it in two separate set operations creates an asymmetry where there is none. We could equally have joined to the women first and then the men.

Answer 1

In SQL Server, I would just join two subqueries with two left joins - if symmetry is what you are looking for:

SELECT *
FROM building
LEFT JOIN (SELECT building, etc. FROM men GROUP BY etc.) AS men_summary
    ON building.name = men_summary.building_name
LEFT JOIN (SELECT building, etc. FROM women GROUP BY etc.) AS women_summary
    ON building.name = women_summary.building_name

I tend to use common table expressions declared first instead of subqueries - it's far more readable (but not ANSI - but then neither is GROUP_CONCAT).

Answer 2

Use Union to combine the data from the men/women tables

select building, [name] as menname, null as womenname from men
union
select building, null as menname, [name] as womenname from women

you now have a single 'table' addmitedly in a subquery against which you can join, count or whatever.

BTW I can see why Cas[s]andra is out in the cold as no-one belives her, but what about dwain, is he similarly cursed by the gods?