SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Oct 2013
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Combining multiple advanced select statements

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

    Code:
    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
    Code:
    +---------------+-----------------+-------------------+-----------------+
    | 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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Oct 2013
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •