Combining multiple advanced select statements

Databases
1

I have three select statements that all use left join, where and group by. These statements are as follows:

mysql> SELECT tenants.tenant_number,
    -> count(employees.emp_id) AS count_employees
    -> FROM tenants
    -> LEFT JOIN employees ON employees.tenant_number = tenants.tenant_number
    -> WHERE tenants.tenant_stat != 6
    -> GROUP BY tenants.tenant_number;
+---------------+-----------------+
| tenant_number | count_employees |
+---------------+-----------------+
|             1 |               7 |
|             4 |               0 |
|             5 |               2 |
|            10 |              86 |
|            49 |             203 |
|            53 |               6 |
|            54 |              18 |
|            64 |               0 |
|            66 |               0 |
|            67 |              10 |
|            68 |               0 |
+---------------+-----------------+
11 rows in set (0.00 sec)

mysql> SELECT tenants.tenant_number,
    -> count(departments.department_number) AS count_departments
    -> FROM tenants
    -> LEFT JOIN departments ON departments.tenants_number = tenants.tenant_number
    -> WHERE tenants.tenant_stat != 6
    -> GROUP BY tenants.tenant_number;
+---------------+-------------------+
| tenant_number | count_departments |
+---------------+-------------------+
|             1 |                 3 |
|             4 |              2845 |
|             5 |              2715 |
|            10 |                 0 |
|            49 |                 0 |
|            53 |                 0 |
|            54 |                 0 |
|            64 |                 0 |
|            66 |                 0 |
|            67 |                 3 |
|            68 |                 4 |
+---------------+-------------------+
11 rows in set (0.02 sec)

mysql> SELECT tenants.tenant_number,
    -> count(altdepartments.department_number) AS count_alt_depts
    -> FROM tenants
    -> LEFT JOIN altdepartments ON altdepartments.tenant_num = tenant.tenant_num
    -> WHERE tenants.tenant_stat != 6
    -> GROUP BY tenants.tenant_num;
+---------------+-----------------+
| tenant_number | count_alt_depts |
+---------------+-----------------+
|             1 |               8 |
|             4 |           19507 |
|             5 |           13628 |
|            10 |               0 |
|            49 |               0 |
|            53 |               0 |
|            54 |               0 |
|            64 |               0 |
|            66 |               0 |
|            67 |               0 |
|            68 |               8 |
+---------------+-----------------+
11 rows in set (0.17 sec)

I’m trying to combine them into a single statement so that the output would look like this

+---------------+-----------------+-------------------+-----------------+
| tenant_number | count_employees | count_departments | count_alt_depts |
+---------------+-----------------+-------------------+-----------------+
|             1 |               7 |                 3 |               8 |
|             4 |               0 |              2845 |           19507 |
|             5 |               2 |              2715 |           13628 |
|            10 |              86 |                 0 |               0 |
|            49 |             203 |                 0 |               0 |
|            53 |               6 |                 0 |               0 |
|            54 |              18 |                 0 |               0 |
|            64 |               0 |                 0 |               0 |
|            66 |               0 |                 0 |               0 |
|            67 |              10 |                 3 |               0 |
|            68 |               0 |                 4 |               8 |
+---------------+-----------------+-------------------+-----------------+

But I’m not having any luck. Any help would be appreciated

2 
SELECT tenants.tenant_number
     , COALESCE(e.count_employees,0) AS count_employees
     , COALESCE(d.count_departments,0) AS count_departments
     , COALESCE(a.count_altdepartments,0) AS count_altdepartments
  FROM tenants
LEFT OUTER
  JOIN ( SELECT tenant_number
              , COUNT(*) AS count_employees
           FROM employees 
         GROUP
             BY tenant_number ) AS e
    ON e.tenant_number = tenants.tenant_number
LEFT OUTER
  JOIN ( SELECT tenant_number
              , COUNT(*) AS count_departments
           FROM departments 
         GROUP
             BY tenant_number ) AS d
    ON d.tenant_number = tenants.tenant_number
LEFT OUTER
  JOIN ( SELECT tenant_number
              , COUNT(*) AS count_altdepartments
           FROM altdepartments 
         GROUP
             BY tenant_number ) AS a
    ON a.tenant_number = tenants.tenant_number
 WHERE tenants.tenant_stat <> 6
3

That does exactly what I was trying to do. I must have spent a couple of hours trying to figure it out but couldn’t.
Thank you very much!