MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
 
Home / Tricky Select Queries / Question No: 60

How to get parent table child table and grandchild table aggregates in MySQL?

How to get parent table aggregates from the child table and per-child table aggregates from the grandchild table in MySQL?

I have parent->child->grandchild tables. First parent table departments, its child table employees and this also has its child table bonuses. The structure and dummy data of these tables are like below:

CREATE TABLE `departments` ( 
`dept_id` mediumint(8) unsigned NOT NULL auto_increment,
`dept_name` varchar(19) default NULL,
PRIMARY KEY (`dept_id`)
) ENGINE=MyISAM;

CREATE TABLE `employees` (
`emp_id` int(10) unsigned NOT NULL auto_increment,
`emp_name` varchar(38) NOT NULL,
`emp_dept_id` mediumint(8) unsigned NOT NULL,
PRIMARY KEY (`emp_id`)
) ENGINE=MyISAM;

CREATE TABLE `bonuses` (
`bonus_id` int(11) NOT NULL default '0',
`bonus_emp_id` int(10) unsigned NOT NULL,
`bonus_date` date default NULL,
`bonus_amount` double unsigned NOT NULL,
PRIMARY KEY (`bonus_id`)
) ENGINE=MyISAM;

INSERT INTO `departments` (`dept_id`, `dept_name`)
VALUES (1, 'HR');
INSERT INTO `departments` (`dept_id`, `dept_name`)
VALUES (2, 'Finance');
INSERT INTO `departments` (`dept_id`, `dept_name`)
VALUES (3, 'Production');
INSERT INTO `departments` (`dept_id`, `dept_name`)
VALUES (4, 'Software');

INSERT INTO `employees` (`emp_id`, `emp_name`, `emp_dept_id`)
VALUES (1, 'Ali', 1);
INSERT INTO `employees` (`emp_id`, `emp_name`, `emp_dept_id`)
VALUES (2, 'Malik', 1);
INSERT INTO `employees` (`emp_id`, `emp_name`, `emp_dept_id`)
VALUES (3, 'Ashlay', 2);
INSERT INTO `employees` (`emp_id`, `emp_name`, `emp_dept_id`)
VALUES (4, 'Nick', 2);
INSERT INTO `employees` (`emp_id`, `emp_name`, `emp_dept_id`)
VALUES (5, 'Nazir', 3);
INSERT INTO `employees` (`emp_id`, `emp_name`, `emp_dept_id`)
VALUES (6, 'Ehsan', 3);
INSERT INTO `employees` (`emp_id`, `emp_name`, `emp_dept_id`)
VALUES (7, 'Sajid', 4);

INSERT INTO `employees` (`emp_id`, `emp_name`, `emp_dept_id`)
VALUES (8, 'Faheem', 4);
INSERT INTO `employees` (`emp_id`, `emp_name`, `emp_dept_id`)
VALUES (9, 'Majid', 4);
INSERT INTO `employees` (`emp_id`, `emp_name`, `emp_dept_id`)
VALUES (10, 'Awais', 4);

INSERT INTO `bonuses`
(`bonus_id`, `bonus_emp_id`, `bonus_date`, `bonus_amount`)
VALUES (1, 5, '2007-10-10', 1000);
INSERT INTO `bonuses`
(`bonus_id`, `bonus_emp_id`, `bonus_date`, `bonus_amount`)
VALUES (2, 5, '2008-01-01', 1500);
INSERT INTO `bonuses`
(`bonus_id`, `bonus_emp_id`, `bonus_date`, `bonus_amount`)
VALUES (3, 8, '2007-06-01', 1000);
INSERT INTO `bonuses`
(`bonus_id`, `bonus_emp_id`, `bonus_date`, `bonus_amount`)
VALUES (4, 8, '2007-10-10', 1000);
INSERT INTO `bonuses`
(`bonus_id`, `bonus_emp_id`, `bonus_date`, `bonus_amount`)
VALUES (5, 8, '2008-02-01', 1000);
I need the results as below:
Department Name        Total Employees         Total Bonuses Achieved
Production 2 2
Software 4 3

Answer No: 60

You may use cascading joins as follow, which can serve your purpose:
SELECT de_1.dept_name AS 'Department Name', 
de_2.total_employee AS 'Total Employees',
eb.total_bonuses AS 'Total Bonuses'
FROM (

SELECT d.dept_id, d.dept_name, e_1.emp_id
FROM departments d
INNER JOIN employees e_1 ON e_1.emp_dept_id = d.dept_id

) AS de_1

INNER JOIN (
SELECT d.dept_id, COUNT(e2.emp_id) AS total_employee
FROM departments d
INNER JOIN employees e2 ON e2.emp_dept_id=d.dept_id
GROUP BY d.dept_id
) AS de_2 ON de_1.dept_id = de_2.dept_id

INNER JOIN (
SELECT e3.emp_id, COUNT( b.bonus_id) AS total_bonuses
FROM employees e3
INNER JOIN bonuses b ON b.bonus_emp_id=e3.emp_id
GROUP BY e3.emp_id
) AS eb ON eb.emp_id = de_1.emp_id;

Related MySQL FAQs to the Above FAQ

How-to-do-numeric-sorting-within-a-string-field-in-MySQL How to do numeric sorting within a string field in MySQL?

How-to-take-data-back-up-of-single-table How to take data back up of single table?

How-to-find-duplicate-records-from-a-table-in-MySQL How to find duplicate records from a table in MySQL?

How-to-get-last-top-records-in-asceding-order How to get last top records in asceding order?

How-to-move-table-from-one-to-another-MySQL-database How to move table from one to another MySQL database?

How-to-skip-repeating-values-from-the-query-results How to skip repeating values from the query results?

How-to-retrieve--one-child-row-against-each-parent-id How to retrieve one child row against each parent id?

An-important-trick-to-speed-up-select-query An important trick to speed up select query.

How-to-find-the-books-which-have-authors-with-more-than-one-book-in-the-table How to find the books which have authors with more than one book in the table?

How-to-count-the-number-of-rows-containing-APOSTROPHE-in-MySQL How to count the number of rows containing APOSTROPHE in MySQL?

How-to-compare-data-of-two-tables-of-two-different-databases-in-MySQL How to compare data of two tables of two different databases in MySQL?

How-to-retreive-multiple-records-without-a-reference-table How to retreive multiple records without a reference table?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2021  www.mysqlfaqs.net
All rights reserved.