CODEWITHSUNDEEP
phpmysql
sammyukavi
sammyukavi

Reputation: 1511

Mysql display information from 4 different tables as one table

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.

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

Upvotes: 0

Views: 54

Answers (3)

Arwin
Arwin

Reputation: 1023

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, but seems like a better answer has already been made.

Upvotes: 0

John Ruddell
John Ruddell

Reputation: 25852

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                |
+-----------------------+-----------------+-----------------+------------------+

Upvotes: 2

Joseph B
Joseph B

Reputation: 5669

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`;

Upvotes: 1

Related Questions