Mysql将来自4个不同表的信息显示为一个表

Question

How can I display information from 4 different tables as one table? I have 4 tables that have related information. These tables contain packages, organisation children and system users data. I want to query what type of package an organisation has subscribed to and how many children and users are registered under that organisation. The sample data contained on the tables is as shown below.

Packages Table
|-------------------------------|
| package_id    | package_name  |
--------------------------------|
| 12            | Basic         |
| 21            | Pro           |
| 33            | Premium       |
---------------------------------

Organisations Table
|-------------------------------------------------------|
| org_id    | org_name                  | package_id    |
|-------------------------------------------------------|
| 18        | Marks of Awesomeness      | 12            |
| 24        | John Hopkins Hospital     | 21            |
| 38        | Teddy and the Wailers     | 33            |
| 78        | Lawrence Movers           | 12            |
|--------------------------------------------------------

Children's Table
|------------------------------------------------|
| id_child      | id_org child_name     | id_org |
|------------------------------------------------|
| 14            | Mark Walker           | 18     |
| 22            | Jane Quinn            | 24     |
| 38            | Lily Audrey           | 24     |
| 44            | Dona Marie            | 18     |
|-------------------------------------------------

Users Table
|------------------------------------------------|
|idu        | org_id        | fname     | lname  |
|------------------------------------------------|
|87         | 18            | John      | Doe    |
|92         | 33            | Jane      | Doe    |
|107        | 18            | Martin    | Short  |
|112        | 18            | Jason     | Seguel |
|127        | 33            | Josh      | Radnor |
|-------------------------------------------------

My query is as shown below

SELECT SQL_CALC_FOUND_ROWS `id_org`, `org_name`, `package_name`
        ,COUNT(id_child) AS child_count, COUNT(idu) AS user_count FROM organisations,packages,children,system_users
        WHERE organisations.id_org=children.org_id AND organisations.id_org=system_users.org_id
     AND organisations.org_package_id=packages.id_package

The problem with the query is that it only shows an organisation only if it has a child and a user listed using its org_id. I want to list all the data for all organisations with the package name it has subscribed to, total number of children listed under that particular organisation, total number of users listed under that particular organisation and zero for where an organisation has no child, a user or both. Below are my desired results. Thanks

Desired Results

|------------------------------------------------------------------------------------|
| Organisation          | package Name      | No of Children    | Number of Users    |
|------------------------------------------------------------------------------------|
| Marks of Awesomeness  | Basic             | 2                 | 3                  |
| John Hopkins Hospital | Pro               | 2                 | 0                  |
| Teddy and the Wailers | Premium           | 0                 | 2                  |
| Lawrence Movers       | Basic             | 0                 | 0                  |
--------------------------------------------------------------------------------------

Answer 1

this will do what you want... you need to left join the two tables that wont have all your records so the users and children... do a count of those and pull that out with COALESCE to handle the null values

NOTE:

your expected results are incorrect... there is no org_id = 33 in the organisation table. Teddy and the Wailers should not have a count of 2 it should be a count of 0 -- OR you want to change the org_id in users to 38 instead of 33.

QUERY:

SELECT 
    o.org_name AS 'Organisation', 
    p.package_name as 'Package Name', 
    COALESCE(t.num_children, 0) AS 'No of Children', 
    COALESCE(t1.num_users, 0) AS 'Number of Users'
FROM organisation o
JOIN packages p ON p.package_id = o.package_id
LEFT JOIN 
(   SELECT 
        COUNT(*) as num_children, id_org 
    FROM children 
    GROUP BY id_org
) as t ON t.id_org = o.org_id 
LEFT JOIN 
(   SELECT 
       COUNT(*) as num_users, org_id 
    FROM users 
    GROUP BY org_id
) as t1 ON t1.org_id = o.org_id

DEMO

OUTPUT:

+-----------------------+-----------------+-----------------+------------------+
| Organisation          | Package Name    | No of Children  | Number of Users  |
+-----------------------+-----------------+-----------------+------------------+
| Marks of Awesomeness  | Basic           | 2               | 3                |
| John Hopkins Hospital | Pro             | 2               | 0                |
| Teddy and the Wailers | Premium         | 0               | 0                |
| Lawrence Movers       | Basic           | 0               | 0                |
+-----------------------+-----------------+-----------------+------------------+

EDIT:

if you change the id 33 to 38 in the users table to match the desired outcome then you will get this result: QUERY

OUTPUT:

+-----------------------+-----------------+-----------------+------------------+
| Organisation          | Package Name    | No of Children  | Number of Users  |
+-----------------------+-----------------+-----------------+------------------+
| Marks of Awesomeness  | Basic           | 2               | 3                |
| John Hopkins Hospital | Pro             | 2               | 0                |
| Teddy and the Wailers | Premium         | 0               | 2                |
| Lawrence Movers       | Basic           | 0               | 0                |
+-----------------------+-----------------+-----------------+------------------+