Multiple Counts - Multiple Tables - 1 Result - Using Left Join

[Alexander Berger]

This is my first time on this site, but I definitely have something to share with the community!

For years I have been trying to find support on a particular issue... a single query that:

Resources Multiple Tables
Counts Column Data from Specified Tables
Returns 1 (column) Result

It just so happens that today, I once again ran into the same situation, but this time I believe that I have finally beaten my nemesis.

I hope the following script helps somebody out.

Note: I had to change my field and table names for security reasons so you must suspend your disbelief and assume that it is possible for a member of a department to be a variety of employee type (e.g. Executive, Admin and Rep)

Note: Also, when used in my robust application the result takes about 20 seconds so if anybody can optimize this query PLEASE LET ME KNOW!

Here is a copy and paste script:

-- ----------------------------------------------------------------------------
-- TABLE: DEPARTMENTS
-- ----------------------------------------------------------------------------
-- department_id | department_name
-- ----------------------------------------------------------------------------
-- 1 |
-- 2 | 123
-- 3 | ABC
-- 4 | JKL
-- 5 | MNO
-- 6 | XYZ
-- 7 | PDQ
-- 8 | LMN
-- ----------------------------------------------------------------------------
-- TABLE: MEMBERS
-- ----------------------------------------------------------------------------
-- member_id | department_id | member_type_id | member_type_ids
-- ----------------------------------------------------------------------------
-- 1 | 1 | 1 |
-- 2 | 3 | 6 |
-- 3 | 6 | 4 | 2:6
-- 4 | 6 | 11 | 6:13
-- 5 | 4 | 11 | 13
-- 6 | 6 | 12 | 4:5
-- 7 | 6 | 6 | 5
-- ----------------------------------------------------------------------------
-- TABLE: MEMBER TYPES
-- ----------------------------------------------------------------------------
-- member_type_id | member_type_name
-- ----------------------------------------------------------------------------
-- 1 |
-- 2 | Temp
-- 3 | HR
-- 4 | IT
-- 5 | Sales
-- 6 | Executive
-- 7 | Officer
-- 8 | Secretary
-- 9 | Developer
-- 10 | Analyst
-- 11 | Manager
-- 12 | Supplier
-- 13 | Rep
-- 14 | Buyer
-- ----------------------------------------------------------------------------
-- TABLE: ATTACHMENTS
-- ----------------------------------------------------------------------------
-- attachment_id | member_id
-- ----------------------------------------------------------------------------
-- 1 | 1
-- 2 | 2
-- 3 | 3
-- 4 | 4
-- 5 | 2
-- 6 | 4
-- 7 | 5
-- ----------------------------------------------------------------------------
-- TABLE: TRANSACTIONS
-- ----------------------------------------------------------------------------
-- transaction_id | department_id
-- ----------------------------------------------------------------------------
-- 1 | 1
-- 2 | 4
-- 3 | 6
-- 4 | 3
-- 5 | 6
-- 6 | 6
-- 7 | 2
-- ----------------------------------------------------------------------------
-- RESULTS
-- ----------------------------------------------------------------------------
-- Department | Members | Executives | Admins | Reps | Attachments | Transactions
-- ----------------------------------------------------------------------------
-- PDQ | 3 | 3 | 1 | 1 | 3 | 3
-- ----------------------------------------------------------------------------
-- TABLE: DEPARTMENTS
-- ----------------------------------------------------------------------------
DROP TABLE IF EXISTS `departments`;
CREATE TABLE IF NOT EXISTS `departments` (
`department_id` tinyint(2) NOT NULL auto_increment,
`department_name` varchar(20) NOT NULL default '',
UNIQUE KEY `department_id` (`department_id`)
) TYPE=MyISAM COMMENT='Table containing department information' AUTO_INCREMENT=8 ;
INSERT INTO `departments` VALUES (1, '');
INSERT INTO `departments` VALUES (2, '123');
INSERT INTO `departments` VALUES (3, 'ABC');
INSERT INTO `departments` VALUES (4, 'JKL');
INSERT INTO `departments` VALUES (5, 'XYZ');
INSERT INTO `departments` VALUES (6, 'PDQ');
INSERT INTO `departments` VALUES (7, 'LMN');
-- ----------------------------------------------------------------------------
-- TABLE: MEMBERS
-- ----------------------------------------------------------------------------
DROP TABLE IF EXISTS `members`;
CREATE TABLE IF NOT EXISTS `members` (
`member_id` int(11) NOT NULL auto_increment,
`department_id` int(11) NOT NULL default '1',
`member_type_id` tinyint(2) NOT NULL default '1',
`member_type_ids` varchar(100) NOT NULL default '',
UNIQUE KEY `member_id` (`member_id`)
) TYPE=MyISAM COMMENT='Table containing member information' AUTO_INCREMENT=8 ;
INSERT INTO `members` VALUES (1, 1, 1, '');
INSERT INTO `members` VALUES (2, 3, 6, '');
INSERT INTO `members` VALUES (3, 6, 4, '2:6');
INSERT INTO `members` VALUES (4, 6, 11, '6:13');
INSERT INTO `members` VALUES (5, 4, 11, '13');
INSERT INTO `members` VALUES (6, 6, 12, '4:5');
INSERT INTO `members` VALUES (7, 6, 6, '5');
-- ----------------------------------------------------------------------------
-- TABLE: MEMBER TYPES
-- ----------------------------------------------------------------------------
DROP TABLE IF EXISTS `member_types`;
CREATE TABLE IF NOT EXISTS `member_types` (
`member_type_id` tinyint(2) NOT NULL auto_increment,
`member_type_name` varchar(20) NOT NULL default '',
UNIQUE KEY `member_type_id` (`member_type_id`)
) TYPE=MyISAM COMMENT='Table containing member type information' AUTO_INCREMENT=15 ;
INSERT INTO `member_types` VALUES (1, '');
INSERT INTO `member_types` VALUES (2, 'Temp');
INSERT INTO `member_types` VALUES (3, 'HR');
INSERT INTO `member_types` VALUES (4, 'IT');
INSERT INTO `member_types` VALUES (5, 'Sales');
INSERT INTO `member_types` VALUES (6, 'Executive');
INSERT INTO `member_types` VALUES (7, 'Officer');
INSERT INTO `member_types` VALUES (8, 'Secretary');
INSERT INTO `member_types` VALUES (9, 'Developer');
INSERT INTO `member_types` VALUES (10, 'Analyst');
INSERT INTO `member_types` VALUES (11, 'Manager');
INSERT INTO `member_types` VALUES (12, 'Supplier');
INSERT INTO `member_types` VALUES (13, 'Rep');
INSERT INTO `member_types` VALUES (14, 'Buyer');
-- ----------------------------------------------------------------------------
-- TABLE: ATTACHMENTS
-- ----------------------------------------------------------------------------
DROP TABLE IF EXISTS `attachments`;
CREATE TABLE IF NOT EXISTS `attachments` (
`attachment_id` int(11) NOT NULL auto_increment,
`member_id` int(11) NOT NULL default '',
UNIQUE KEY `attachment_id` (`attachment_id`)
) TYPE=MyISAM COMMENT='Table containing attachment information' AUTO_INCREMENT=8 ;
INSERT INTO `attachments` VALUES (1, 1);
INSERT INTO `attachments` VALUES (2, 2);
INSERT INTO `attachments` VALUES (3, 3);
INSERT INTO `attachments` VALUES (4, 4);
INSERT INTO `attachments` VALUES (5, 2);
INSERT INTO `attachments` VALUES (6, 4);
INSERT INTO `attachments` VALUES (7, 5);
-- ----------------------------------------------------------------------------
-- TABLE: TRANSACTIONS
-- ----------------------------------------------------------------------------
DROP TABLE IF EXISTS `transactions`;
CREATE TABLE IF NOT EXISTS `transactions` (
`transaction_id` int(11) NOT NULL auto_increment,
`department_id` tinyint(2) NOT NULL default '',
UNIQUE KEY `transaction_id` (`transaction_id`)
) TYPE=MyISAM COMMENT='Table containing transaction information' AUTO_INCREMENT=8 ;
INSERT INTO `transactions` VALUES (1, 1);
INSERT INTO `transactions` VALUES (2, 4);
INSERT INTO `transactions` VALUES (3, 6);
INSERT INTO `transactions` VALUES (4, 3);
INSERT INTO `transactions` VALUES (5, 6);
INSERT INTO `transactions` VALUES (6, 6);
INSERT INTO `transactions` VALUES (7, 2);
-- ----------------------------------------------------------------------------
-- RESULT
-- ----------------------------------------------------------------------------
SELECT
d.department_name as Department,
COUNT(DISTINCT(m.member_id)) as Members,
COUNT(DISTINCT(me.member_id)) as Executives,
COUNT(DISTINCT(mm.member_id)) as Managers,
COUNT(DISTINCT(mr.member_id)) as Reps,
COUNT(DISTINCT(a.attachment_id)) as Attachments,
COUNT(DISTINCT(t.transaction_id)) as Transactions
FROM
departments as d
LEFT JOIN
members as m
ON
m.member_type_id = 6 OR m.member_type_ids LIKE '%6%' OR m.member_type_id = 11 OR m.member_type_ids LIKE '%11%' OR m.member_type_id = 13 OR m.member_type_ids LIKE '%13%'
LEFT JOIN
members as me
ON
me.member_type_id = 6 OR me.member_type_ids LIKE '%6%'
LEFT JOIN
members as mm
ON
mm.member_type_id = 11 OR mm.member_type_ids LIKE '%11%'
LEFT JOIN
members as mr
ON
mr.member_type_id = 13 OR mr.member_type_ids LIKE '%13%'
LEFT JOIN
members as ma,
attachments as a
ON
a.member_id = ma.member_id AND
ma.department_id = d.department_id
LEFT JOIN
transactions as t
ON
t.department_id = d.department_id
WHERE
d.department_id = 6
GROUP BY
d.department_name
LIMIT 1;