SQL COUNT 0 when joining from 3 tables

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`

Open in new window


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

Open in new window


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`

Open in new window


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

Open in new window


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 ;

Open in new window


Any help on this would be greatly appreciated.  I have been banging my head against the wall for quite a while on this one!
LVL 1
bdhtechnologyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Louis01Commented:
This what you wanted?
SELECT 
	IFNULL(COUNT(*),0) AS `Count`,
	l.`location` AS `Location`,
	IFNULL(`script_type`, 'None Selected') AS `Prescription Type`
FROM `locations` l
LEFT OUTER JOIN (
	SELECT 
		`locationid`,
		`script_numid`
	FROM `errors` 
) 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`
GROUP BY l.`location`, `script_type`
ORDER BY `Location`, `Prescription Type`

Open in new window

0
bdhtechnologyAuthor Commented:
Unfortunately no, that produces the same results except with a count of 1 instead of 0 when there are no rows:
+-------+---------------------------------+-------------------+
| Count | Location                        | Prescription Type |
+-------+---------------------------------+-------------------+
|     1 | xxxxxx 01 - Coralville          | None Selected     |
|     1 | xxxxxx 02 - Coralville          | None Selected     |
|     1 | 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       |
|     1 | xxxxxx 15 - Elmhurst            | None Selected     |
|     1 | xxxxxx 16 - Ottumwa             | R - Refill        |
|     1 | xxxxxx 17 - Austin              | None Selected     |
|     8 | xxxxxx 20 - Steege (West Union) | R - Refill        |
|     1 | xxxxxx 21 - Duluth              | N - New           |
|     1 | xxxxxx 23 - Paynesville         | None Selected     |
|     1 | 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        |
|     1 | xxxxxx 27 - Pleasant Hill       | None Selected     |
|     3 | xxxxxx 28 - Ames                | N - New           |
|     3 | xxxxxx 28 - Ames                | R - Refill        |
|     8 | xxxxxx 29 - Zearing             | N - New           |
|     1 | xxxxxx 30 - Lenox               | None Selected     |
|     1 | xxxxxx 31 - North Dakota        | None Selected     |
|     1 | xxxxxx 32 - Fairfield           | None Selected     |
+-------+---------------------------------+-------------------+

Open in new window


Basically what I would like to see would be something like this:
+-------+---------------------------------+-------------------+
| Count | Location                        | Prescription Type |
+-------+---------------------------------+-------------------+
|     0 | xxxxxx 01 - Coralville          | None Selected     |
|     0 | xxxxxx 01 - Coralville          | N - New           |
|     0 | xxxxxx 01 - Coralville          | R - Refill        |
|     0 | xxxxxx 01 - Coralville          | U - Updated       |
|     0 | xxxxxx 02 - Coralville          | None Selected     |
|     0 | xxxxxx 02 - Coralville          | N - New           |
|     0 | xxxxxx 02 - Coralville          | R - Refill        |
|     0 | xxxxxx 02 - Coralville          | U - Updated       |
|     0 | xxxxxx 04 - Waterloo            | None Selected     |
|     0 | xxxxxx 04 - Waterloo            | N - New           |
|     0 | xxxxxx 04 - Waterloo            | R - Refill        |
|     0 | xxxxxx 04 - Waterloo            | U - Updated       |
|     0 | xxxxxx 06 - Ackley              | None Selected     |
|    39 | xxxxxx 06 - Ackley              | N - New           |
|    15 | xxxxxx 06 - Ackley              | R - Refill        |
|    10 | xxxxxx 06 - Ackley              | U - Updated       |
--- continued like that
+-------+---------------------------------+-------------------+

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
actually, you seem to be missing some "prescription type" items, right?
for example:
|    20 | xxxxxx 10 - Story City          | N - New           |
|    20 | xxxxxx 10 - Story City          | R - Refill        |

you are missing the 3rd row for the U - Update , to be 0

please confirm
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

PortletPaulfreelancerCommented:
Could you try this please?
SELECT
       IFNULL(COUNT(e.`locationid`), 0) AS `Count`
     , l.`location` AS `Location`
     , IFNULL(`script_type`, 'None Selected') AS `Prescription Type`
FROM `nuhealthwellness-com`.`locations` l
LEFT JOIN  `nucaracqi-com`.`errors`  e ON l.`locationid` = e.`locationid`
LEFT 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`
GROUP BY
       l.`location`
     , s.`script_type`
ORDER BY
       IFNULL(COUNT(e.`locationid`), 0)
     , l.`location`
     , case when ISNULL(`script_type`) then 1
            when script_type` = 'N - New' then 2
            when script_type` = 'R - Refill' then 3
            when script_type` = 'U - Updated' then 4
            else 5
       end;

Open in new window

as there's no data I'm unable to test it of course.
0
bdhtechnologyAuthor Commented:
@angelIII, yes that is correct, it is not showing me a 0 when the count is 0 for a location/Prescription Type.
0
bdhtechnologyAuthor Commented:
@PortletPaul, that gives me the same results as the second query in my original post.

SELECT
       IFNULL(COUNT(e.`locationid`), 0) AS `Count`
     , l.`location` AS `Location`
     , IFNULL(`script_type`, 'None Selected') AS `Prescription Type`
FROM `locations` l
LEFT JOIN  `errors`  e ON l.`locationid` = e.`locationid`
LEFT 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`
GROUP BY
       l.`location`
     , s.`script_type`
ORDER BY
 `Location`, `Prescription Type`
     , case when ISNULL(`script_type`) then 1
            when `script_type` = 'N - New' then 2
            when `script_type` = 'R - Refill' then 3
            when `script_type` = 'U - Updated' then 4
            else 5
       end

Open in new window

Result:
+-------+---------------------------------+-------------------+
| 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     |
|     8 | xxxxxx 20 - xxxxxx (West Union) | R - Refill        |
|     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     |
+-------+---------------------------------+-------------------+

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this is indeed a bit trickier than what I thought in the beginning ...
anyhow, I would start with something like this (untested) code:
SELECT 
	l.`location` AS `Location`,
	IFNULL( CONCAT( s.`script_num_value`, ' - ', s.`script_num_description`) , 'None Selected') AS `Prescription Type`
FROM `locations` l
LEFT JOIN (  
	( SELECT e.`locationid`, e.`script_numid`, COUNT(*)  errcnt
	FROM `errors`  e
       GROUP BY e.`locationid`, e.`script_numid`
) `eg`
   ON l.`locationid` = `eg`.`locationid`
 LEFT JOIN `script_nums` s
      ON `eg`.errcnt > 0 
WHERE isnull(`eg`.script_numid, s.`script_num_value`) = s.`script_num_value`
        OR `eg`.errcnt = 0
ORDER BY `Location`,  s.`script_num_value` 

Open in new window

0
PortletPaulfreelancerCommented:
ps: in the comment above "where isnull(" should be "where iFnull(" I believe
----

>>Basically what I would like to see would be something like this:
+-------+---------------------------------+-------------------+
| Count | Location                        | Prescription Type |
+-------+---------------------------------+-------------------+
|     0 | xxxxxx 01 - Coralville          | None Selected     |
|     0 | xxxxxx 01 - Coralville          | N - New           |
|     0 | xxxxxx 01 - Coralville          | R - Refill        |
|     0 | xxxxxx 01 - Coralville          | U - Updated       |
|     0 | xxxxxx 02 - Coralville          | None Selected     |
|     0 | xxxxxx 02 - Coralville          | N - New           |
|     0 | xxxxxx 02 - Coralville          | R - Refill        |
|     0 | xxxxxx 02 - Coralville          | U - Updated       |
|     0 | xxxxxx 04 - Waterloo            | None Selected     |
|     0 | xxxxxx 04 - Waterloo            | N - New           |

Open in new window

Is this sort order important ?

I had proposed the following to achieve that order:
ORDER BY
       IFNULL(COUNT(e.`locationid`), 0)
     , l.`location`
     , case when ISNULL(`script_type`) then 1
            when script_type` = 'N - New' then 2
            when script_type` = 'R - Refill' then 3
            when script_type` = 'U - Updated' then 4
            else 5
       end;

Open in new window

0
bdhtechnologyAuthor Commented:
@angelIII, I ran the query (with IFNULL instead of ISNULL, thanks @PortletPaul):
SELECT 
	l.`location` AS `Location`,
	IFNULL( CONCAT( s.`script_num_value`, ' - ', s.`script_num_description`) , 'None Selected') AS `Prescription Type`
FROM `locations` l
LEFT JOIN (  
	SELECT e.`locationid`, e.`script_numid`, COUNT(*)  errcnt
	FROM `errors`  e
	GROUP BY e.`locationid`, e.`script_numid`
) `eg`ON l.`locationid` = `eg`.`locationid`
LEFT JOIN `script_nums` s
ON `eg`.errcnt > 0 
WHERE IFNULL(eg.`script_numid`, s.`script_num_value`) = s.`script_num_value`
	OR `eg`.errcnt = 0
ORDER BY `Location`,  s.`script_num_value`

Open in new window


However it returned an empty set
0
bdhtechnologyAuthor Commented:
@PortletPaul, the order is not super important, as I can sort it in PHP later, however ideally it would be sorted by location, then by Prescription Type.  

Maybe I am asking too much from SQL.  I can achieve this pretty simply by selecting all of the locationid's in a separate query, then doing a foreach loop on them.  I was just hoping to reduce the number of SQL calls since this is for a report, and I will have to perform 7 more lookups similar to this one.
0
PortletPaulfreelancerCommented:
It will be possible to achieve this via SQL I feel certain, it's a little difficult to achieve this way due to lack of test data I'm afraid. See if this helps.
SELECT
        l.`location` AS `Location`
      , IFNULL(`script_type`, 'None Selected') AS `Prescription Type`
      , IFNULL(e.num_of, 0) AS `Count`
FROM `locations` AS l
LEFT JOIN (
            SELECT
                    `locationid`
                  , `script_numid`
                  , count(*) AS num_of
            FROM `errors`
            GROUP BY
                    `locationid`
                  , `script_numid`
          ) AS E ON l.`locationid` = e.`locationid`
LEFT JOIN (
             SELECT
                   `script_numid`
                  , CONCAT (
                           `script_num_value`
                           , ' - '
                           , `script_num_description`
                           ) AS `script_type`
             FROM `script_nums`
            ) AS S ON e.`script_numid` = s.`script_numid`
;

Open in new window

Note an inherent assumption here is that `script_nums` does not also need to be grouped in some way. I haven't worried this time about order and will leave that to you.

If this continues to miss the mark, I would ask for some sample data so we may run these queries before submitting.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
let's see, this is some tested SQL:
SELECT 
	l.location  AS  Location 
,  MAX ( case when eg.errcnt is null then 0 when eg.script_numid = s.script_numid then eg.errcnt else 0 end) errcnt 
, IFNULL(  CONCAT( s.script_num_value , ' - ' , s.script_num_description  ) , 'None Selected') AS  Prescription_Type 
FROM  locations  l
LEFT JOIN 
	( SELECT e.locationid , e.script_numid , COUNT(*)  errcnt
	FROM  errors   e
       GROUP BY e.locationid , e.script_numid 
)  eg 
   ON l.locationid  =  eg.locationid 
LEFT JOIN  script_nums  s
      ON  eg.errcnt > 0 
group by l.location
 , IFNULL(  CONCAT( s.script_num_value , ' - ' , s.script_num_description  ) , 'None Selected')
ORDER BY  Location 
, IFNULL(  CONCAT( s.script_num_value , ' - ' , s.script_num_description  ) , 'None Selected')

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
comment above update to remove trailing comma
0
bdhtechnologyAuthor Commented:
@PortletPaulPosted [ID: 39483469]

Same result I posted in ID: 39480683
0
bdhtechnologyAuthor Commented:
@angelIII [ID: 39483714]

Same result I posted in ID: 39480683

Unfortunately I can't post any sample data from the errors table, due to confidentiality issues.  I can post sample data from the other tables if that is helpful?
0
PortletPaulfreelancerCommented:
>> I can post sample data from the other tables if that is helpful?
yes please

>>I can't post any sample data from the errors table, due to confidentiality issues
alter values so they are no longer confidential (obsfucate)

we don't need text of errors, names, or anything confidential, we just need data that represents it
e.g.
12345, 'private private private private',code1

and as long as we can join the tables so that it represents what you have there.

For all the above: we don't need a vast amount of data
0
bdhtechnologyAuthor Commented:
Locations:
CREATE TABLE IF NOT EXISTS `locations` (
  `locationid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `modtimestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last Modification',
  `deleted` int(1) unsigned DEFAULT '0' COMMENT 'Deleted?',
  `orgid` int(10) unsigned NOT NULL COMMENT 'Organization',
  `location` varchar(100) NOT NULL COMMENT 'Location',
  PRIMARY KEY (`locationid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=332 ;

INSERT INTO `locations` (`locationid`, `modtimestamp`, `deleted`, `orgid`, `location`) VALUES
(3, '2013-08-31 13:02:59', 0, 2, 'xxxxxx 06 - Ackley'),
(13, '2013-08-31 13:09:14', 0, 2, 'xxxxxx 28 - Ames'),
(23, '2013-08-31 13:05:44', 0, 2, 'xxxxxx 17 - Austin'),
(33, '2013-08-31 13:07:16', 0, 2, 'xxxxxx 26 - Carroll'),
(43, '2013-08-31 13:03:15', 0, 2, 'xxxxxx 07 - Conrad'),
(53, '2013-08-31 13:02:17', 0, 2, 'xxxxxx 02 - Coralville'),
(63, '2013-08-31 13:06:02', 0, 2, 'xxxxxx 21 - Duluth'),
(73, '2013-08-31 13:05:07', 0, 2, 'xxxxxx 15 - Elmhurst'),
(83, '2013-08-31 13:06:58', 0, 2, 'xxxxxx 25 - Marshalltown North'),
(93, '2013-08-31 13:06:40', 0, 2, 'xxxxxx 24 - Marshalltown South'),
(103, '2013-08-31 13:03:34', 0, 2, 'xxxxxx 09 - Nevada'),
(113, '2013-08-31 13:05:25', 0, 2, 'xxxxxx 16 - Ottumwa'),
(123, '2013-08-31 13:06:20', 0, 2, 'xxxxxx 23 - Paynesville'),
(133, '2013-08-31 13:07:33', 0, 2, 'xxxxxx 27 - Pleasant Hill'),
(143, '2013-08-31 13:03:54', 0, 2, 'xxxxxx 10 - Story City'),
(153, '2013-08-31 13:04:38', 0, 2, 'xxxxxx 12 - Traer'),
(163, '2013-08-31 13:04:17', 0, 2, 'xxxxxx 11 - Washington'),
(173, '2013-08-31 13:02:41', 0, 2, 'xxxxxx 04 - Waterloo'),
(183, '2013-09-04 00:07:41', 0, 2, 'xxxxxx 20 - West Union'),
(272, '2013-08-31 13:09:28', 0, 2, 'xxxxxx 30 - Lenox'),
(282, '2013-08-31 13:09:44', 0, 2, 'xxxxxx 29 - Zearing'),
(292, '2013-09-04 00:06:59', 0, 2, 'xxxxxx 31 - North Dakota'),
(302, '2013-08-31 13:01:57', 0, 2, 'xxxxxx 01 - Coralville'),
(322, '2013-09-04 00:08:18', 0, 2, 'xxxxxx 32 - Fairfield');

Open in new window


script_nums:
CREATE TABLE IF NOT EXISTS `script_nums` (
  `script_numid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `modtimestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last Modification',
  `deleted` int(1) DEFAULT '0' COMMENT 'Deleted?',
  `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 ;

INSERT INTO `script_nums` (`script_numid`, `modtimestamp`, `deleted`, `script_num_value`, `script_num_description`) VALUES
(1, '2013-01-23 18:56:16', 0, 'N', 'New'),
(2, '2012-12-28 22:32:24', 0, 'R', 'Refill'),
(3, '2012-12-28 22:32:24', 0, 'U', 'Updated');

Open in new window


I will work on sanitizing the errors table to post as well.
0
bdhtechnologyAuthor Commented:
errors:
CREATE TABLE IF NOT EXISTS `errors` (
  `errorid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `modtimestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last Modification',
  `deleted` int(1) unsigned DEFAULT NULL COMMENT 'Deleted?',
  `locationid` int(10) unsigned NOT NULL COMMENT 'NuCara 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=1922 ;

--
-- Dumping data for table `errors`
--

INSERT INTO `errors` (`errorid`, `modtimestamp`, `deleted`, `locationid`, `cqidate`, `script_numid`, `script_num`, `error_typeid`, `error_locid`, `patient_outcomeid`, `actionplanid`, `notes`, `prescribed_drug`, `dispensed_drug`, `patient_typeid`) VALUES
(2, '2013-09-13 12:59:07', 0, 143, '2013-03-11 17:48:24', 2, 0, 11, 15, 2, 1, '', '', '', 1),
(12, '2013-09-13 12:59:07', 0, 143, '2013-03-11 17:49:57', 2, 0, 2, 13, 1, 1, '', '', '', 1),
(22, '2013-09-13 12:59:07', 0, 13, '2013-03-11 17:57:08', 2, 0, 11, 15, 2, 1, '', '', '', 1),
(32, '2013-09-13 12:59:07', 0, 163, '2013-03-12 13:39:56', 1, 0, 3, 37, 1, 1, '', '', '', 1),
(42, '2013-09-13 12:59:07', 0, 143, '2013-03-12 18:08:22', 1, 0, 3, 7, 1, 1, '', '', '', 1),
(52, '2013-09-13 12:59:07', 0, 203, '2013-03-12 18:25:12', 2, 0, 11, 15, 2, 1, '', '', '', 1),
(62, '2013-09-13 12:59:07', 0, 143, '2013-03-14 09:42:27', 2, 0, 2, 13, 1, 1, '', '', '', 1),
(72, '2013-09-13 12:59:07', 0, 143, '2013-03-14 11:50:30', 1, 0, 2, 13, 1, 1, '', '', '', 1),
(82, '2013-09-13 12:59:07', 0, 143, '2013-03-14 16:30:22', 1, 0, 9, 6, 1, 1, '', '', '', 1),
(92, '2013-09-13 12:59:07', 0, 3, '2013-03-14 17:49:16', 2, 0, 1, 12, 1, 1, '', '', '', 1),
(102, '2013-09-13 12:59:07', 0, 3, '2013-03-14 17:50:59', 3, 0, 1, 12, 1, 1, '', '', '', 1),
(112, '2013-09-13 12:59:07', 0, 3, '2013-03-14 17:53:06', 1, 0, 1, 6, 2, 1, '', '', '', 1),
(122, '2013-09-13 12:59:07', 0, 3, '2013-03-14 17:54:48', 1, 0, 3, 7, 1, 1, '', '', '', 1),
(132, '2013-09-13 12:59:07', 0, 3, '2013-03-14 17:56:38', 1, 0, 1, 3, 2, 1, '', '', '', 1),
(142, '2013-09-13 12:59:07', 0, 3, '2013-03-15 14:34:44', 1, 0, 1, 3, 1, 1, '', '', '', 1),
(152, '2013-09-13 12:59:07', 0, 3, '2013-03-15 16:29:57', 3, 0, 2, 6, 1, 1, '', '', '', 1),
(162, '2013-09-13 12:59:07', 0, 203, '2013-03-19 09:35:45', 1, 0, 1, 3, 1, 1, '', '', '', 1),
(172, '2013-09-13 12:59:07', 0, 143, '2013-03-20 12:26:39', 1, 0, 11, 15, 1, 1, '', '', '', 1),
(182, '2013-09-13 12:59:07', 0, 143, '2013-03-20 16:21:45', 1, 0, 9, 11, 1, 1, '', '', '', 1),
(192, '2013-09-13 12:59:07', 0, 143, '2013-03-21 11:42:42', 1, 0, 3, 7, 1, 1, '', '', '', 1),
(202, '2013-09-13 12:59:07', 0, 3, '2013-03-22 15:19:07', 1, 0, 1, 3, 1, 1, '', '', '', 1),
(212, '2013-09-13 12:59:07', 0, 3, '2013-03-23 09:03:21', 1, 0, 2, 6, 1, 1, '', '', '', 1),
(222, '2013-09-13 12:59:07', 0, 3, '2013-03-25 10:31:29', 1, 0, 11, 15, 1, 1, '', '', '', 1),
(232, '2013-09-13 12:59:07', 0, 143, '2013-03-25 14:50:56', 2, 0, 2, 13, 1, 1, '', '', '', 1),
(242, '2013-09-13 12:59:07', 0, 3, '2013-03-26 16:59:17', 3, 0, 2, 13, 1, 1, '', '', '', 1),
(252, '2013-09-13 12:59:07', 0, 3, '2013-03-29 11:00:24', 3, 0, 11, 15, 1, 1, '', '', '', 1),
(262, '2013-09-13 12:59:07', 0, 3, '2013-03-29 11:03:21', 2, 0, 9, 14, 1, 1, '', '', '', 1),
(272, '2013-09-13 12:59:07', 0, 143, '2013-03-29 15:05:06', 2, 0, 1, 12, 1, 1, '', '', '', 1),
(282, '2013-09-13 12:59:07', 0, 3, '2013-04-01 10:21:06', 1, 0, 11, 15, 1, 1, '', '', '', 1),
(292, '2013-09-13 12:59:07', 0, 3, '2013-04-01 11:16:16', 2, 0, 11, 14, 1, 1, '', '', '', 1),
(302, '2013-09-13 12:59:07', 0, 3, '2013-04-01 17:29:33', 1, 0, 2, 13, 1, 1, '', '', '', 1),
(312, '2013-09-13 12:59:07', 0, 143, '2013-04-02 14:46:11', 2, 0, 1, 12, 1, 1, '', '', '', 1),
(322, '2013-09-13 12:59:07', 0, 143, '2013-04-03 14:56:45', 2, 0, 1, 12, 1, 1, '', '', '', 1),
(332, '2013-09-13 12:59:07', 0, 153, '2013-04-04 14:55:06', 2, 0, 1, 12, 1, 1, '', '', '', 1),
(342, '2013-09-13 12:59:07', 0, 153, '0000-00-00 00:00:00', 2, 0, 9, 32, 2, 1, '', '', '', 1),
(352, '2013-09-13 12:59:07', 0, 153, '0000-00-00 00:00:00', 2, 0, 9, 32, 2, 1, '', '', '', 1),
(362, '2013-09-13 12:59:07', 0, 153, '2013-04-04 15:01:26', 3, 0, 3, 7, 1, 1, '', '', '', 1),
(372, '2013-09-13 12:59:07', 0, 143, '2013-04-04 15:01:59', 1, 0, 9, 6, 1, 1, '', '', '', 1),
(382, '2013-09-13 12:59:07', 0, 153, '2013-04-04 15:03:26', 1, 0, 1, 3, 1, 1, '', '', '', 1),
(392, '2013-09-13 12:59:07', 0, 3, '2013-04-04 15:27:26', 1, 0, 1, 6, 1, 1, '', '', '', 1),
(402, '2013-09-13 12:59:07', 0, 3, '2013-04-05 10:16:12', 1, 0, 2, 6, 1, 1, '', '', '', 1),
(412, '2013-09-13 12:59:07', 0, 3, '2013-04-08 10:28:01', 1, 0, 9, 4, 1, 1, '', '', '', 1),
(422, '2013-09-13 12:59:07', 0, 143, '2013-04-08 12:03:49', 2, 0, 2, 13, 1, 1, '', '', '', 1),
(432, '2013-09-13 12:59:07', 0, 83, '2013-04-08 13:42:08', 1, 0, 1, 3, 1, 1, '', '', '', 1),
(442, '2013-09-13 12:59:07', 0, 3, '2013-04-08 14:40:57', 1, 0, 2, 6, 1, 1, '', '', '', 1),
(452, '2013-09-13 12:59:07', 0, 43, '0000-00-00 00:00:00', 1, 0, 9, 32, 2, 1, '', '', '', 1),
(462, '2013-09-13 12:59:07', 0, 143, '2013-04-11 14:02:55', 1, 0, 3, 7, 1, 1, '', '', '', 1),
(472, '2013-09-13 12:59:07', 0, 3, '2013-04-15 15:12:47', 1, 0, 2, 13, 1, 1, '', '', '', 1),
(482, '2013-09-13 12:59:07', 0, 143, '2013-04-15 15:21:30', 1, 0, 9, 6, 1, 1, '', '', '', 1),
(492, '2013-09-13 12:59:07', 0, 13, '2013-04-19 11:25:06', 1, 0, 8, 32, 2, 1, '', '', '', 1),
(502, '2013-09-13 12:59:07', 0, 143, '2013-04-23 17:41:02', 1, 0, 1, 12, 1, 1, '', '', '', 1),
(512, '2013-09-13 12:59:07', 0, 3, '2013-04-24 10:17:02', 1, 0, 2, 13, 1, 1, '', '', '', 1),
(522, '2013-09-13 12:59:07', 0, 3, '2013-04-24 13:02:03', 2, 0, 2, 13, 1, 1, '', '', '', 1),
(532, '2013-09-13 12:59:07', 0, 143, '2013-04-24 16:31:42', 1, 0, 2, 6, 1, 1, '', '', '', 1),
(542, '2013-09-13 12:59:07', 0, 143, '2013-04-25 10:31:35', 2, 0, 2, 13, 1, 1, '', '', '', 1),
(552, '2013-09-13 12:59:07', 0, 3, '2013-04-25 11:39:12', 1, 0, 9, 4, 1, 1, '', '', '', 1),
(562, '2013-09-13 12:59:07', 0, 3, '2013-04-25 12:48:17', 2, 0, 11, 15, 1, 1, '', '', '', 1),
(572, '2013-09-13 12:59:07', 0, 3, '2013-04-26 17:24:27', 1, 0, 1, 3, 1, 1, '', '', '', 1),
(582, '2013-09-13 12:59:07', 0, 3, '2013-04-29 11:20:19', 1, 0, 3, 7, 1, 1, '', '', '', 1),
(592, '2013-09-13 12:59:07', 0, 3, '2013-04-29 13:41:15', 1, 0, 2, 6, 1, 1, '', '', '', 1),
(602, '2013-09-13 12:59:07', 0, 143, '2013-04-29 15:15:59', 2, 0, 2, 13, 1, 1, '', '', '', 1),
(612, '2013-09-13 12:59:07', 0, 33, '0000-00-00 00:00:00', 1, 0, 13, 8, 2, 1, '', '', '', 1),
(622, '2013-09-13 12:59:07', 0, 33, '0000-00-00 00:00:00', 1, 0, 2, 13, 2, 1, '', '', '', 1),
(632, '2013-09-13 12:59:07', 0, 33, '2013-04-30 10:25:28', 1, 0, 3, 7, 2, 1, '', '', '', 1),
(642, '2013-09-13 12:59:07', 0, 33, '0000-00-00 00:00:00', 2, 0, 2, 13, 2, 1, '', '', '', 1),
(652, '2013-09-13 12:59:07', 0, 3, '2013-04-30 12:21:35', 1, 0, 1, 3, 1, 1, '', '', '', 1),
(662, '2013-09-13 12:59:07', 0, 3, '2013-04-30 18:46:05', 1, 0, 9, 4, 1, 1, '', '', '', 1),
(672, '2013-09-13 12:59:07', 0, 113, '2013-03-15 00:00:00', 2, 0, 11, 15, 2, 1, '', '', '', 1),
(682, '2013-09-13 12:59:07', 0, 143, '2013-05-02 09:41:13', 2, 0, 2, 6, 1, 1, '', '', '', 1),
(692, '2013-09-13 12:59:07', 0, 103, '2013-05-02 14:55:39', 2, 0, 11, 15, 2, 1, '', '', '', 1),
(702, '2013-09-13 12:59:07', 0, 143, '2013-05-02 16:44:28', 1, 0, 2, 13, 1, 1, '', '', '', 1),
(712, '2013-09-13 12:59:07', 0, 3, '2013-05-03 10:02:05', 2, 0, 11, 15, 1, 1, '', '', '', 1),
(722, '2013-09-13 12:59:07', 0, 3, '2013-05-03 12:00:27', 3, 0, 1, 14, 1, 1, '', '', '', 1),
(732, '2013-09-13 12:59:07', 0, 3, '2013-05-03 12:02:17', 3, 0, 11, 15, 1, 1, '', '', '', 1),
(742, '2013-09-13 12:59:07', 0, 3, '2013-05-06 09:08:54', 2, 0, 2, 13, 1, 1, '', '', '', 1),
(752, '2013-09-13 12:59:07', 0, 3, '2013-04-11 00:00:00', 1, 0, 3, 7, 2, 1, '', '', '', 1),
(762, '2013-09-13 12:59:07', 0, 63, '2013-05-06 16:08:45', 1, 0, 2, 3, 1, 1, '', '', '', 1),
(772, '2013-09-13 12:59:07', 0, 3, '2013-05-07 09:55:26', 2, 0, 11, 15, 1, 1, '', '', '', 1),
(782, '2013-09-13 12:59:07', 0, 3, '2013-05-07 11:18:14', 2, 0, 11, 15, 1, 1, '', '', '', 1),
(792, '2013-09-13 12:59:07', 0, 143, '2013-05-07 17:48:28', 2, 0, 1, 3, 1, 1, '', '', '', 1),
(802, '2013-09-13 12:59:07', 0, 143, '2013-05-07 17:49:53', 2, 0, 11, 15, 1, 1, '', '', '', 1),
(812, '2013-09-13 12:59:07', 0, 83, '2013-05-08 09:06:19', 1, 0, 3, 7, 1, 1, '', '', '', 1),
(822, '2013-09-13 12:59:07', 0, 3, '2013-05-07 00:00:00', 3, 0, 2, 13, 1, 1, '', '', '', 1),
(832, '2013-09-13 12:59:07', 0, 3, '2013-05-08 16:57:42', 1, 0, 2, 6, 1, 1, '', '', '', 1),
(842, '2013-09-13 12:59:07', 0, 3, '2013-05-09 16:18:53', 1, 0, 1, 3, 1, 1, '', '', '', 1),
(852, '2013-09-13 12:59:07', 0, 3, '2013-05-09 00:00:00', 1, 0, 1, 3, 1, 1, '', '', '', 1),
(862, '2013-09-13 12:59:07', 0, 3, '2013-05-10 12:15:52', 1, 0, 9, 15, 1, 1, '', '', '', 1),
(872, '2013-09-13 12:59:07', 0, 13, '2013-04-01 00:00:00', 1, 0, 2, 20, 2, 1, '', '', '', 2),
(882, '2013-09-13 12:59:07', 0, 83, '2013-05-13 09:02:38', 1, 0, 5, 8, 1, 1, '', '', '', 1),
(892, '2013-09-13 12:59:07', 0, 103, '2013-05-13 13:02:40', 1, 0, 3, 7, 1, 1, '', '', '', 1),
(902, '2013-09-13 12:59:07', 0, 143, '2013-05-13 14:46:45', 1, 0, 3, 7, 1, 1, '', '', '', 1),
(912, '2013-09-13 12:59:07', 0, 83, '2013-05-16 11:14:51', 1, 0, 8, 4, 1, 1, '', '', '', 1),
(922, '2013-09-13 12:59:07', 0, 143, '2013-05-16 17:33:53', 1, 0, 2, 6, 1, 1, '', '', '', 1),
(932, '2013-09-13 12:59:07', 0, 103, '2013-05-20 12:40:10', 1, 0, 9, 4, 1, 1, '', '', '', 1),
(942, '2013-09-13 12:59:07', 0, 103, '2013-05-20 12:41:13', 1, 0, 3, 7, 1, 1, '', '', '', 1),
(952, '2013-09-13 12:59:07', 0, 103, '2013-05-20 13:03:24', 2, 0, 2, 13, 1, 1, '', '', '', 1),
(962, '2013-09-13 12:59:07', 0, 103, '2013-05-20 13:04:37', 2, 0, 1, 12, 1, 1, '', '', '', 1),
(972, '2013-09-13 12:59:07', 0, 103, '2013-05-20 14:50:21', 1, 0, 2, 6, 1, 1, '', '', '', 1),
(982, '2013-09-13 12:59:07', 0, 103, '2013-05-20 17:46:50', 1, 0, 11, 15, 1, 1, '', '', '', 1),
(992, '2013-09-13 12:59:07', 0, 282, '2013-05-21 15:46:49', 1, 0, 9, 1, 1, 1, '', '', '', 1),
(1002, '2013-09-13 12:59:07', 0, 282, '2013-05-20 00:00:00', 1, 0, 3, 7, 1, 1, '', '', '', 1),
(1012, '2013-09-13 12:59:07', 0, 143, '2013-05-22 13:12:18', 2, 0, 2, 13, 1, 1, '', '', '', 1),
(1022, '2013-09-13 12:59:07', 0, 103, '2013-05-23 10:34:24', 1, 0, 1, 3, 1, 1, '', '', '', 1),
(1032, '2013-09-13 12:59:07', 0, 3, '2013-05-23 00:00:00', 2, 0, 1, 12, 1, 1, '', '', '', 1),
(1042, '2013-09-13 12:59:07', 0, 143, '2013-05-24 10:25:56', 2, 0, 2, 6, 1, 1, '', '', '', 1),
(1052, '2013-09-13 12:59:07', 0, 3, '2013-05-24 14:11:45', 1, 0, 1, 3, 1, 1, '', '', '', 1),
(1062, '2013-09-13 12:59:07', 0, 103, '2013-05-24 14:22:17', 1, 0, 3, 7, 1, 1, '', '', '', 1),
(1072, '2013-09-13 12:59:07', 0, 103, '2013-05-24 14:24:40', 1, 0, 11, 15, 1, 1, '', '', '', 1),
(1082, '2013-09-13 12:59:07', 0, 83, '2013-05-24 16:54:31', 1, 0, 2, 3, 2, 1, '', '', '', 1),
(1092, '2013-09-13 12:59:07', 0, 143, '2013-05-24 17:52:26', 1, 0, 2, 6, 1, 1, '', '', '', 1),
(1102, '2013-09-13 12:59:07', 0, 143, '2013-05-28 09:45:42', 2, 0, 2, 13, 1, 1, '', '', '', 1),
(1112, '2013-09-13 12:59:07', 0, 3, '2013-05-28 15:19:53', 1, 0, 2, 6, 1, 1, '', '', '', 1),
(1122, '2013-09-13 12:59:07', 0, 143, '2013-05-30 10:29:17', 1, 0, 1, 12, 1, 1, '', '', '', 1),
(1132, '2013-09-13 12:59:07', 0, 143, '2013-05-29 00:00:00', 2, 0, 1, 13, 2, 1, '', '', '', 1),
(1142, '2013-09-13 12:59:07', 0, 143, '2013-05-24 00:00:00', 1, 0, 9, 4, 1, 1, '', '', '', 1),
(1152, '2013-09-13 12:59:07', 0, 43, '2013-06-05 09:40:25', 2, 0, 9, 15, 1, 1, '', '', '', 1),
(1162, '2013-09-13 12:59:07', 0, 43, '2013-06-05 09:42:48', 2, 0, 9, 18, 1, 1, '', '', '', 1),
(1172, '2013-09-13 12:59:07', 0, 43, '2013-06-05 09:43:54', 2, 0, 9, 18, 1, 1, '', '', '', 1),
(1182, '2013-09-13 12:59:07', 0, 83, '2013-06-05 12:53:56', 1, 0, 2, 13, 1, 1, '', '', '', 1),
(1192, '2013-09-13 12:59:07', 0, 3, '2013-06-06 10:29:43', 1, 0, 2, 6, 1, 1, '', '', '', 1),
(1202, '2013-09-13 12:59:07', 0, 3, '2013-06-06 12:54:58', 1, 0, 3, 7, 1, 1, '', '', '', 1),
(1212, '2013-09-13 12:59:07', 0, 3, '2013-06-03 00:00:00', 1, 0, 9, 11, 1, 1, '', '', '', 1),
(1222, '2013-09-13 12:59:07', 0, 3, '2013-06-03 00:00:00', 2, 0, 11, 15, 1, 1, '', '', '', 1),
(1232, '2013-09-13 12:59:07', 0, 43, '2013-06-06 17:08:44', 2, 0, 9, 18, 1, 1, '', '', '', 1),
(1242, '2013-09-13 12:59:07', 0, 43, '2013-06-06 17:10:49', 2, 0, 9, 15, 1, 1, '', '', '', 1),
(1252, '2013-09-13 12:59:07', 0, 43, '2013-06-06 17:12:02', 2, 0, 9, 15, 1, 1, '', '', '', 1),
(1262, '2013-09-13 12:59:07', 0, 43, '2013-06-06 17:13:09', 2, 0, 9, 15, 1, 1, '', '', '', 1),
(1272, '2013-09-13 12:59:07', 0, 43, '2013-06-06 17:17:04', 2, 0, 9, 15, 1, 1, '', '', '', 1),
(1282, '2013-09-13 12:59:07', 0, 43, '2013-06-06 17:18:26', 2, 0, 9, 15, 1, 1, '', '', '', 1),
(1292, '2013-09-13 12:59:07', 0, 43, '2013-06-06 17:19:26', 2, 0, 9, 15, 1, 1, '', '', '', 1),
(1302, '2013-09-13 12:59:07', 0, 43, '2013-06-07 11:16:24', 2, 0, 8, 31, 2, 1, '', '', '', 1),
(1312, '2013-09-13 12:59:07', 0, 103, '2013-06-07 12:37:06', 2, 0, 11, 15, 1, 1, '', '', '', 1),
(1322, '2013-09-13 12:59:07', 0, 103, '2013-06-07 12:38:14', 3, 0, 9, 11, 1, 1, '', '', '', 1),
(1332, '2013-09-13 12:59:07', 0, 103, '2013-06-07 12:40:43', 2, 0, 1, 19, 1, 1, '', '', '', 1),
(1342, '2013-09-13 12:59:07', 0, 103, '2013-06-07 14:06:56', 2, 0, 11, 15, 1, 1, '', '', '', 1),
(1352, '2013-09-13 12:59:07', 0, 3, '2013-06-07 17:22:45', 3, 0, 2, 6, 1, 1, '', '', '', 1),
(1362, '2013-09-13 12:59:07', 0, 13, '2013-06-06 00:00:00', 2, 0, 11, 15, 2, 1, '', '', '', 1),
(1372, '2013-09-13 12:59:07', 0, 103, '2013-06-11 10:25:56', 1, 0, 9, 11, 1, 1, '', '', '', 1),
(1382, '2013-09-13 12:59:07', 0, 103, '2013-06-11 10:27:02', 1, 0, 9, 11, 1, 1, '', '', '', 1),
(1392, '2013-09-13 12:59:07', 0, 103, '2013-06-11 16:02:05', 3, 0, 11, 15, 1, 1, '', '', '', 1),
(1402, '2013-09-13 12:59:07', 0, 3, '2013-06-12 11:43:01', 3, 0, 1, 12, 1, 1, '', '', '', 1),
(1412, '2013-09-13 12:59:07', 0, 3, '2013-06-12 17:34:40', 1, 0, 2, 6, 1, 1, '', '', '', 1),
(1422, '2013-09-13 12:59:07', 0, 252, '2013-01-01 00:00:00', 1, 0, 1, 12, 2, 1, '', '', '', 1),
(1432, '2013-09-13 12:59:07', 0, 252, '2013-04-04 00:00:00', 1, 0, 9, 2, 2, 1, '', '', '', 1),
(1442, '2013-09-13 12:59:07', 0, 252, '2013-03-20 00:00:00', 1, 0, 9, 18, 1, 1, '', '', '', 1),
(1452, '2013-09-13 12:59:07', 0, 83, '2013-06-13 13:15:20', 1, 0, 2, 6, 1, 1, '', '', '', 1),
(1462, '2013-09-13 12:59:07', 0, 143, '2013-06-17 14:32:26', 1, 0, 3, 7, 1, 1, '', '', '', 1),
(1472, '2013-09-13 12:59:07', 0, 43, '2013-06-13 00:00:00', 2, 0, 9, 18, 1, 1, '', '', '', 1),
(1482, '2013-09-13 12:59:07', 0, 43, '2013-06-13 00:00:00', 2, 0, 9, 18, 1, 1, '', '', '', 1),
(1492, '2013-09-13 12:59:07', 0, 282, '2013-06-18 12:22:29', 1, 0, 3, 7, 1, 1, '', '', '', 1),
(1502, '2013-09-13 12:59:07', 0, 13, '2013-06-18 17:54:13', 1, 0, 2, 6, 2, 1, '', '', '', 1),
(1512, '2013-09-13 12:59:07', 0, 3, '2013-06-19 12:57:27', 2, 0, 2, 13, 1, 1, '', '', '', 1),
(1522, '2013-09-13 12:59:07', 0, 43, '2013-06-19 17:27:05', 3, 0, 1, 12, 1, 1, '', '', '', 1),
(1532, '2013-09-13 12:59:07', 0, 3, '2013-06-21 16:52:45', 1, 0, 1, 12, 1, 1, '', '', '', 1),
(1542, '2013-09-13 12:59:07', 0, 3, '2013-06-20 00:00:00', 3, 0, 3, 7, 1, 1, '', '', '', 1),
(1552, '2013-09-13 12:59:07', 0, 3, '2013-06-20 00:00:00', 1, 0, 3, 7, 1, 1, '', '', '', 1),
(1562, '2013-09-13 12:59:07', 0, 83, '2013-06-24 12:07:55', 1, 0, 9, 11, 1, 1, '', '', '', 1),
(1572, '2013-09-13 12:59:07', 0, 83, '2013-06-24 12:09:57', 1, 0, 3, 7, 1, 1, '', '', '', 1),
(1582, '2013-09-13 12:59:07', 0, 3, '2013-06-24 12:40:56', 1, 0, 2, 6, 1, 1, '', '', '', 1),
(1592, '2013-09-13 12:59:07', 0, 3, '2013-06-25 10:43:43', 1, 0, 2, 6, 1, 1, '', '', '', 1),
(1602, '2013-09-13 12:59:07', 0, 3, '2013-06-25 12:52:30', 1, 0, 1, 3, 1, 1, '', '', '', 1),
(1612, '2013-09-13 12:59:07', 0, 3, '2013-06-26 11:39:34', 2, 0, 2, 6, 1, 1, '', '', '', 1),
(1622, '2013-09-13 12:59:07', 0, 83, '2013-06-27 15:06:33', 3, 0, 2, 13, 1, 1, '', '', '', 1),
(1632, '2013-09-13 12:59:07', 0, 83, '2013-06-27 15:42:09', 1, 0, 2, 13, 1, 1, '', '', '', 1),
(1642, '2013-09-13 12:59:07', 0, 83, '2013-06-27 15:43:34', 1, 0, 11, 11, 1, 1, '', '', '', 1),
(1652, '2013-09-13 12:59:07', 0, 3, '2013-06-27 15:44:28', 1, 0, 2, 6, 1, 1, '', '', '', 1),
(1662, '2013-09-13 12:59:07', 0, 3, '2013-06-27 17:20:19', 1, 0, 1, 3, 1, 1, '', '', '', 1),
(1672, '2013-09-13 12:59:07', 0, 282, '2013-06-28 09:58:23', 1, 0, 3, 7, 1, 1, '', '', '', 1),
(1682, '2013-09-13 12:59:07', 0, 43, '2013-07-01 16:57:51', 1, 0, 1, 1, 1, 1, '', '', '', 1),
(1692, '2013-09-13 12:59:07', 0, 252, '2013-07-02 13:03:42', 1, 0, 11, 15, 2, 1, '', '', '', 6),
(1702, '2013-09-13 12:59:07', 0, 252, '2013-07-01 00:00:00', 1, 0, 11, 15, 2, 1, '', '', '', 1),
(1712, '2013-09-13 12:59:07', 0, 183, '2013-07-09 14:03:45', 2, 0, 12, 11, 2, 1, '', '', '', 1),
(1722, '2013-09-13 12:59:07', 0, 183, '2013-07-09 14:04:31', 2, 0, 12, 11, 2, 1, '', '', '', 1),
(1732, '2013-09-13 12:59:07', 0, 282, '2013-07-09 15:14:07', 1, 0, 9, 3, 1, 1, '', '', '', 1),
(1742, '2013-09-13 12:59:07', 0, 83, '2013-07-09 17:00:59', 1, 0, 9, 11, 1, 1, '', '', '', 1),
(1752, '2013-09-13 12:59:07', 0, 3, '2013-07-11 12:22:30', 2, 0, 1, 12, 1, 1, '', '', '', 1),
(1762, '2013-09-13 12:59:07', 0, 183, '2013-07-11 00:00:00', 2, 0, 1, 29, 2, 1, '', '', '', 1),
(1772, '2013-09-13 12:59:07', 0, 183, '2013-07-12 10:55:41', 2, 0, 1, 29, 2, 1, '', '', '', 1),
(1782, '2013-09-13 12:59:07', 0, 183, '2013-07-15 11:04:23', 2, 0, 1, 12, 1, 1, '', '', '', 1),
(1792, '2013-09-13 12:59:07', 0, 183, '2013-07-15 12:01:12', 2, 0, 2, 13, 1, 1, '', '', '', 1),
(1802, '2013-09-13 12:59:07', 0, 3, '2013-07-16 15:58:38', 2, 0, 2, 13, 1, 1, '', '', '', 1),
(1812, '2013-09-13 12:59:07', 0, 183, '2013-07-18 00:00:00', 2, 0, 1, 6, 1, 1, '', '', '', 1),
(1822, '2013-09-13 12:59:07', 0, 183, '2013-07-18 00:00:00', 2, 0, 2, 13, 1, 1, '', '', '', 1),
(1832, '2013-09-13 12:59:07', 0, 282, '2013-07-22 16:49:34', 1, 0, 5, 8, 1, 2, '', '', '', 1),
(1842, '2013-09-13 12:59:07', 0, 43, '2013-07-30 14:38:25', 3, 0, 7, 18, 1, 2, '', '', '', 1),
(1852, '2013-09-13 12:59:07', 0, 43, '2013-07-30 14:40:15', 2, 0, 2, 13, 1, 2, '', '', '', 1),
(1862, '2013-09-13 12:59:07', 0, 103, '2013-08-07 00:00:00', 1, 0, 1, 3, 2, 6, '', '', '', 1),
(1872, '2013-09-13 12:59:07', 0, 83, '2013-08-15 13:14:31', 1, 0, 13, 8, 1, 2, '', '', '', 1),
(1882, '2013-09-13 12:59:07', 0, 103, '2013-08-20 11:09:30', 1, 0, 11, 15, 2, 1, '', '', '', 1),
(1892, '2013-09-13 12:59:07', 0, 282, '2013-08-22 09:32:54', 1, 0, 3, 11, 1, 2, '', '', '', 1),
(1902, '2013-09-13 12:59:07', 0, 282, '2013-08-23 15:02:05', 1, 0, 9, 11, 1, 2, '', '', '', 1),
(1912, '2013-09-13 12:59:07', 0, 13, '2013-08-24 09:15:10', 2, 0, 9, 39, 2, 5, '', '', '', 2);

Open in new window

0
bdhtechnologyAuthor Commented:
Unfortunately I was not able to solve this problem through SQL only.  I accomplished it via a PHP loop and SQL
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.