Databases
StackOverflow2013
Postsdbo
POIs it possible to report on 2 tables without using a subquery?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIs it possible to report on 2 tables without using a subquery?
    text
    copied!<p>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</p> <pre><code>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; </code></pre> <p>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.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload