如何将四个不同表中的信息显示为一个表？我有4张桌子
具有相关信息。这些表包含数据包，组织子级和
系统用户数据。我想查询一个组织订阅了哪种类型的软件包，以及该组织下注册了多少个子代和用户。表格中包含的示例数据如下所示。
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 |
|-------------------------------------------------
我的查询如下
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
该查询的问题在于，仅在有孩子和用户的情况下才显示组织
使用其org_id列出。我想列出所有组织的所有数据及其已订阅的软件包名称，
该特定组织列出的儿童总数，下面列出的用户总数
该特定组织，对于没有子组织，用户或两者皆无的组织，则为零。
以下是我想要的结果。谢谢
所需结果
|------------------------------------------------------------------------------------|
| 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 |
--------------------------------------------------------------------------------------
最佳答案
这将完成您想要的工作...您需要将没有所有记录的两个表连接在一起，以便用户和子项...对这些计数进行计数，并使用COALESCE将其取出以处理空值
注意：
您的预期结果不正确...组织表中没有org_id = 33。 Teddy and the Wailers不应为2，而应为0-或您要将用户中的org_id更改为38而不是33。
查询：
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
输出：
+-----------------------+-----------------+-----------------+------------------+
| 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 |
+-----------------------+-----------------+-----------------+------------------+
编辑：
如果将用户表中的ID 33更改为38以匹配所需的结果，则会得到以下结果：QUERY
输出：
+-----------------------+-----------------+-----------------+------------------+
| 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 |
+-----------------------+-----------------+-----------------+------------------+