troubleshooting Question

SQL COUNT 0 when joining from 3 tables

Avatar of bdhtechnology
bdhtechnology asked on
MySQL ServerSQL
19 Comments2 Solutions340 ViewsLast Modified:
I have a table that I want to select data from two other tables and count the results, even if they are 0.  I have one query working that joins two tables together.  However when I try and join the 3rd I am not getting the results I want.  Here is the code that works for the 2 tables:
SELECT 
	IFNULL(e.`errcnt`,0) AS `Count`,
	l.`location` AS `Location`
FROM `nuhealthwellness-com`.`locations` l
LEFT OUTER JOIN (
	SELECT 
		COUNT(*) AS `errcnt`,
		`locationid`,
		`script_numid`
	FROM `nucaracqi-com`.`errors` 
	GROUP BY `locationid`
) e ON l.`locationid`=e.`locationid`
ORDER BY `Location`

This produces output like:
+-------+---------------------------------+
| Count | Location                        |
+-------+---------------------------------+
|     0 | xxxxxx 01 - Coralville          |
|     0 | xxxxxx 02 - Coralville          |
|     0 | xxxxxx 04 - Waterloo            |
|    64 | xxxxxx 06 - Ackley              |
|    18 | xxxxxx 07 - Conrad              |
|    20 | xxxxxx 09 - Nevada              |
|    40 | xxxxxx 10 - Story City          |
|     1 | xxxxxx 11 - Washington          |
|     5 | xxxxxx 12 - Traer               |
|     0 | xxxxxx 15 - Elmhurst            |
|     1 | xxxxxx 16 - Ottumwa             |
|     0 | xxxxxx 17 - Austin              |
|     1 | xxxxxx 21 - Duluth              |
|     0 | xxxxxx 23 - Paynesville         |
|     0 | xxxxxx 24 - Marshalltown South  |
|    14 | xxxxxx 25 - Marshalltown North  |
|     4 | xxxxxx 26 - Carroll             |
|     0 | xxxxxx 27 - Pleasant Hill       |
|     6 | xxxxxx 28 - Ames                |
|     8 | xxxxxx 29 - Zearing             |
|     0 | xxxxxx 30 - Lenox               |
|     0 | xxxxxx 31 - North Dakota        |
|     0 | xxxxxx 32 - Fairfield           |
+-------+---------------------------------+

However when I try to join the 3rd table I am not see the results I am looking for.  I want to join the 3rd table to select: CONCAT(`script_num_value`, ' - ', `script_num_description`) AS `Prescription Type` including NULL values

The query below gets me pretty close, however it doesn't return a count of 0 for all the values from the script_nums table.
SELECT 
	IFNULL(e.`errcnt`,0) AS `Count`,
	l.`location` AS `Location`,
	IFNULL(`script_type`, 'None Selected') AS `Prescription Type`
FROM `locations` l
LEFT OUTER JOIN (
	SELECT 
		COUNT(*) AS `errcnt`,
		`locationid`,
		`script_numid`
	FROM `errors` 
	GROUP BY `locationid`, `script_numid`
) e ON l.`locationid`=e.`locationid`
LEFT OUTER JOIN (
	SELECT
		`script_numid`,
		CONCAT(`script_num_value`, ' - ', `script_num_description`) AS `script_type`
	FROM `script_nums`
	ORDER BY `script_type`
) s ON e.`script_numid`=s.`script_numid`
ORDER BY `Location`, `Prescription Type`

Which yields the results of the below.  This is almost there, except I need to get the count for all the values for each location, even if the count is 0.
+-------+---------------------------------+-------------------+
| Count | Location                        | Prescription Type |
+-------+---------------------------------+-------------------+
|     0 | xxxxxx 01 - Coralville          | None Selected     |
|     0 | xxxxxx 02 - Coralville          | None Selected     |
|     0 | xxxxxx 04 - Waterloo            | None Selected     |
|    39 | xxxxxx 06 - Ackley              | N - New           |
|    15 | xxxxxx 06 - Ackley              | R - Refill        |
|    10 | xxxxxx 06 - Ackley              | U - Updated       |
|     2 | xxxxxx 07 - Conrad              | N - New           |
|    14 | xxxxxx 07 - Conrad              | R - Refill        |
|     2 | xxxxxx 07 - Conrad              | U - Updated       |
|    12 | xxxxxx 09 - Nevada              | N - New           |
|     6 | xxxxxx 09 - Nevada              | R - Refill        |
|     2 | xxxxxx 09 - Nevada              | U - Updated       |
|    20 | xxxxxx 10 - Story City          | N - New           |
|    20 | xxxxxx 10 - Story City          | R - Refill        |
|     1 | xxxxxx 11 - Washington          | N - New           |
|     1 | xxxxxx 12 - Traer               | N - New           |
|     3 | xxxxxx 12 - Traer               | R - Refill        |
|     1 | xxxxxx 12 - Traer               | U - Updated       |
|     0 | xxxxxx 15 - Elmhurst            | None Selected     |
|     1 | xxxxxx 16 - Ottumwa             | R - Refill        |
|     0 | xxxxxx 17 - Austin              | None Selected     |
|     1 | xxxxxx 21 - Duluth              | N - New           |
|     0 | xxxxxx 23 - Paynesville         | None Selected     |
|     0 | xxxxxx 24 - Marshalltown South  | None Selected     |
|    13 | xxxxxx 25 - Marshalltown North  | N - New           |
|     1 | xxxxxx 25 - Marshalltown North  | U - Updated       |
|     3 | xxxxxx 26 - Carroll             | N - New           |
|     1 | xxxxxx 26 - Carroll             | R - Refill        |
|     0 | xxxxxx 27 - Pleasant Hill       | None Selected     |
|     3 | xxxxxx 28 - Ames                | N - New           |
|     3 | xxxxxx 28 - Ames                | R - Refill        |
|     8 | xxxxxx 29 - Zearing             | N - New           |
|     0 | xxxxxx 30 - Lenox               | None Selected     |
|     0 | xxxxxx 31 - North Dakota        | None Selected     |
|     0 | xxxxxx 32 - Fairfield           | None Selected     |
+-------+---------------------------------+-------------------+

Here are the full table definitions
CREATE TABLE IF NOT EXISTS `errors` (
  `errorid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `locationid` int(10) unsigned NOT NULL COMMENT 'Pharmacy',
  `cqidate` datetime NOT NULL COMMENT 'Date',
  `script_numid` int(10) unsigned NOT NULL COMMENT 'Prescription Type',
  `script_num` int(8) NOT NULL COMMENT 'Prescription #',
  `error_typeid` int(10) unsigned NOT NULL COMMENT 'What type of error?',
  `error_locid` int(10) unsigned NOT NULL COMMENT 'Where was the error made?',
  `patient_outcomeid` int(10) unsigned NOT NULL COMMENT 'Patient Outcome',
  `actionplanid` int(10) unsigned NOT NULL COMMENT 'Action Plan',
  `notes` varchar(500) NOT NULL COMMENT 'Pharmacy Notes',
  `prescribed_drug` varchar(100) NOT NULL COMMENT 'Prescribed Drug',
  `dispensed_drug` varchar(100) NOT NULL COMMENT 'Dispensed Drug',
  `patient_typeid` int(10) unsigned NOT NULL COMMENT 'Type of Patient',
  PRIMARY KEY (`errorid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1972 ;

CREATE TABLE IF NOT EXISTS `locations` (
  `locationid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `location` varchar(100) NOT NULL COMMENT 'Location',
  PRIMARY KEY (`locationid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=332 ;

CREATE TABLE IF NOT EXISTS `script_nums` (
  `script_numid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `script_num_value` varchar(2) NOT NULL COMMENT 'Value',
  `script_num_description` varchar(50) NOT NULL COMMENT 'Description',
  PRIMARY KEY (`script_numid`),
  UNIQUE KEY `script_num_value` (`script_num_value`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

Any help on this would be greatly appreciated.  I have been banging my head against the wall for quite a while on this one!
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 19 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 19 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros