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 |
--------------------------------------------------------------------------------------
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
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.
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
+-----------------------+-----------------+-----------------+------------------+
| 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 |
+-----------------------+-----------------+-----------------+------------------+
if you change the id 33 to 38 in the users table to match the desired outcome then you will get this result: 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 |
+-----------------------+-----------------+-----------------+------------------+
You need to use a join:
select org.org_name, pack.package_name, count(child.id_child) as NoOfChildren, count(user.idu) as NoOfUsers from Organisation as org
join Packages as pack
on pack.package_id = org.package_id
left outer join Children as child on child.id_org = org.org_id
left outer join Users as user on user.org_id = org.org_id
group by org.org_name, pack.package_name
And the updated fiddle: http://sqlfiddle.com/#!2/cfe16/14/0
Still not completely right though, the counts are off. Have to go to bed though, will look again later, but seems like a better answer has already been made.
Instead of the implicit INNER JOIN that you use now, try using LEFT JOINs and the COALESCE function to handle NULL values, as below:
SELECT
SQL_CALC_FOUND_ROWS `id_org`,
`org_name`,
`package_name`,
COALESCE(COUNT(id_child), 0) AS child_count,
COALESCE(COUNT(idu), 0) AS user_count
FROM organisations
INNER JOIN packages ON organisations.org_package_id=packages.id_package
LEFT JOIN children ON organisations.id_org=children.org_id
LEFT JOIN system_users ON organisations.id_org=system_users.org_id
GROUP BY SQL_CALC_FOUND_ROWS, `org_name`, `package_name`;