sql query not giving the expected results

[Drewser33]

SELECT *, Count(CustomerNumber) AS custCount FROM tblTickets
LEFT JOIN tblTicketMessages ON tblTickets.ID =tblTicketMessages.TicketID
WHERE Type = 1
AND CustomerNumber = '000000'
AND ((tblTickets.DateOpened >= '2009-02-28' AND tblTickets.DateOpened <= '2009-03-01') OR (tblTicketMessages.Date >= '2009-02-28' AND tblTicketMessages.Date <= '2009-03-01'))

GROUP BY CustomerNumber

ORDER BY custCount DESC;

 

That is the query that exists now, but for some reason it is adding in a count when tblTicketMessages.Date is outside of the end date parameter (2009-3-13).

 

So the count should be 1 because the tblticket.DateOpened = 2009-02-28 and the tblticketmessage.Date = 2009-03-13.

 

But my count is = 2?

 

Any ideas?

[fenway]

Schema? Sample data?

 

And * and GROUP BY are like oil & water.

[Drewser33]

Table tblTickets

ID    CustomerNumber      DateOpened

1        000000                    2009-02-26

2        000000                    2009-02-28

 

Table tblTicketMessages

ID    TicketID    Date

1        1            2009-02-27

2        1            2009-03-01

3        1            2009-03-03

4        2            2009-03-04

 

If the parameters for the "DateFrom" is 2009-02-28 and the "DateTo" is 2009-02-28 the count should return 1.

 

If the parameters for the "DateFrom" is 2009-02-28 and the "DateTo" is 2009-03-03 the count should return 3.

 

Hope this helps and thank you for the assistance.

[fenway]

Perhaps because you specified a non-left-join'ed field in COUNT()?  If you provided sql statements to generate the tables & rows, I could test some other ideas on my end.

[Drewser33]

CREATE TABLE IF NOT EXISTS `tbltickets` (

  `ID` int(10) NOT NULL auto_increment,

  `CustomerNumber` varchar(50) default NULL,

  `Contact` varchar(50) default NULL,

  `ContactPhone` varchar(10) default NULL,

  `Extension` varchar(15) default NULL,

  `TroubleID` int(10) default NULL,

  `Summary` longtext,

  `OpenedBy` varchar(50) default NULL,

  `DateOpened` datetime default NULL,

  `Status` tinyint(1) NOT NULL default '0',

  `DateClosed` datetime default NULL,

  `ClosedBy` varchar(50) default NULL,

  `FollowUp` tinyint(1) NOT NULL default '0',

  `DateFollowUp` date default NULL,

  `Type` int(1) NOT NULL default '0',

  `warrantyActivity` varchar(5) default NULL,

  PRIMARY KEY  (`ID`),

  KEY `tblFacilitiestblTickets` (`CustomerNumber`),

  KEY `TroubleID` (`TroubleID`),

  FULLTEXT KEY `Summary` (`Summary`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11520 ;

 

CREATE TABLE IF NOT EXISTS `tblticketmessages` (

  `ID` int(10) NOT NULL auto_increment,

  `TicketID` int(10) default NULL,

  `Message` longtext character set latin1,

  `EnteredBy` varchar(50) character set latin1 default NULL,

  `Date` datetime default NULL,

  PRIMARY KEY  (`ID`),

  KEY `TicketID` (`TicketID`),

  FULLTEXT KEY `Message` (`Message`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=12309 ;

 

Here are the table structures.  Again - thanks for the help.

[fenway]

Give me a few INSERT statements, and I'll get to work...

[Drewser33]

--

-- Dumping data for table `tbltickets`

--

 

INSERT INTO `tbltickets` (`ID`, `CustomerNumber`, `Contact`, `ContactPhone`, `Extension`, `TroubleID`, `Summary`, `OpenedBy`, `DateOpened`, `Status`, `DateClosed`, `ClosedBy`, `FollowUp`, `DateFollowUp`, `Type`, `warrantyActivity`) VALUES
(11490, '000000', 'test', '', '', NULL, 'test test', '1', '2009-02-11 08:55:15', -1, '2009-02-12 12:46:57', '1', 0, NULL, 0, ''),
(11491, '000000', 'test', '', '', NULL, 'test', '1', '2009-02-12 13:23:08', -1, '2009-02-24 08:37:50', '1', 0, NULL, 0, ''),
(11492, '000000', 'tesfasd', '', '', NULL, 'teate asdf', '1', '2009-02-12 13:23:15', -1, '2009-02-13 12:31:26', '1', 0, NULL, 0, ''),
(11493, '000000', 'sdfsdf', '', '', NULL, 'teasdfsd', '1', '2009-02-13 18:33:35', -1, '2009-02-24 08:37:02', '1', 0, NULL, 1, ''),
(11494, '500642', 'Steve', '', '', NULL, 'Got a new keypad and needs programming.', '1', '2009-02-23 10:55:46', -1, '2009-02-23 10:56:06', '1', 0, NULL, 0, ''),
(11495, '000000', 'teset', '', '', NULL, 'test', '1', '2009-02-24 09:44:17', -1, '2009-02-24 13:53:57', '1', 0, NULL, 0, ''),
(11496, '000000', 'Test Test', '', '', NULL, 'Testing with new ticket', '1', '2009-02-24 13:54:15', -1, '2009-02-24 14:00:58', '1', 0, NULL, 0, ''),
(11497, '000000', 'Test Name', '2620007458', '', NULL, 'Test ticket number 2. Number 2. Number 2. We are having problems and need it fixed. Run away. Run away.', '1', '2009-02-24 14:04:15', -1, '2009-02-24 14:10:18', '1', 0, NULL, 0, ''),
(11498, '000000', 'Test Name', '', '', NULL, 'Test', '1', '2009-02-25 07:54:07', -1, '2009-02-25 08:08:55', '1', 0, NULL, 0, ''),
(11499, '000000', 'Test Name', '', '', NULL, 'Test', '1', '2009-02-25 08:09:02', -1, '2009-02-25 09:04:03', '1', 0, NULL, 0, ''),
(11500, '000000', 'Test Name', '', '', NULL, 'test', '1', '2009-02-25 10:34:54', -1, '2009-02-25 10:35:42', '1', 0, NULL, 0, ''),
(11501, '501089', 'Katie', '', '', NULL, 'Just received new pager and it''s not working.', '1', '2009-02-25 10:44:54', -1, '2009-02-25 10:49:20', '1', 0, NULL, 0, ''),
(11506, '000000', 'Test Name', '', '', NULL, 'test', '1', '2009-02-26 09:55:53', 0, NULL, NULL, 0, NULL, 0, ''),
(11507, '000000', 'Test Name', '', '', NULL, 'test', '1', '2009-02-26 10:06:56', -1, '2009-02-27 12:54:37', '1', 0, NULL, 1, ''),
(11508, '500940', 'Dean', '2003067706', '', NULL, 'Left me a VM. Wants to discuss the conversation that I had earlier with Chris.', '1', '2009-02-27 13:11:50', -1, '2009-02-27 13:46:49', '1', 0, NULL, 0, ''),
(11509, '000000', 'Test Name', '', '', NULL, 'test', '1', '2009-02-28 22:41:58', 2, NULL, NULL, 0, NULL, 1, ''),
(11510, '000000', 'test', '', '', NULL, 'test proactive', '1', '2009-03-13 07:46:28', 0, NULL, NULL, 0, NULL, 3, ''),
(11511, '000000', 'Drew', '4140004757', NULL, NULL, NULL, '2', '2009-03-16 16:18:26', -1, '2009-03-16 00:00:00', NULL, 0, NULL, 3, NULL),
(11512, '000000', 'Drew', '4140004757', NULL, NULL, NULL, '2', '2009-03-16 16:18:47', -1, '2009-03-16 00:00:00', NULL, 0, NULL, 3, NULL),
(11513, '000000', 'Drew', '4140004757', NULL, NULL, 'ddddddddd', '2', '2009-03-16 16:23:14', -1, '2009-03-16 17:23:14', NULL, 0, NULL, 3, NULL),
(11514, '000000', '', '', NULL, NULL, 'sdfasdfasdfasdf\r\nasdfasf\r\nasdf\r\nasdf\r\nasdf\r\n', '2', '2009-03-16 16:26:27', -1, '2009-03-16 17:26:27', NULL, 0, NULL, 3, NULL),
(11515, '000000', 'dfasf', '3333333333', NULL, NULL, 'ddddd\r\ndddddd\r\ndddddd\r\ndddddd', '2', '2009-03-16 16:27:15', -1, '2009-03-16 17:27:15', NULL, 0, NULL, 3, NULL),
(11516, '000000', 'tttttttt', '4444444444', NULL, NULL, 'dafasdfad\r\ndfasf\r\ndf\r\nasdf\r\nasfd', '2', '2009-03-16 16:37:30', -1, '2009-03-16 17:37:30', NULL, 0, NULL, 3, NULL),
(11517, '000000', 'tttttttt', '4444444444', NULL, NULL, 'dafasdfad\r\ndfasf\r\ndf\r\nasdf\r\nasfd', '2', '2009-03-16 16:37:41', -1, '2009-03-16 17:37:41', NULL, 0, NULL, 3, NULL),
(11518, '000000', 'tttttttt', '4444444444', NULL, NULL, 'dafasdfad\r\ndfasf\r\ndf\r\nasdf\r\nasfd', '2', '2009-03-16 16:38:25', -1, '2009-03-16 17:38:25', NULL, 0, NULL, 3, NULL),
(11519, '000000', 'sdfasdfasd', '4444444444', NULL, NULL, 'afasdfasdf<br />\r\nasfasdf<br />\r\nf<br />\r\nasdf<br />\r\nasdf<br />\r\n', '2', '2009-03-16 16:39:44', -1, '2009-03-16 17:39:44', NULL, 0, NULL, 3, NULL);

 

--

-- Dumping data for table `tblticketmessages`

--

 

INSERT INTO `tblticketmessages` (`ID`, `TicketID`, `Message`, `EnteredBy`, `Date`) VALUES
(12282, 11492, 'test', '1', '2009-02-13 08:41:50'),
(12283, 11493, 'blah blah', '1', '2009-02-20 14:05:26'),
(12284, 11493, 'tests test tests test', '1', '2009-02-20 14:05:47'),
(12285, 11494, 'Told him I would fax the programming sheet that we previously created for him. Fax#: (888) 888-2672.', '1', '2009-02-23 10:55:58'),
(12286, 11495, 'test test test', '1', '2009-02-24 13:47:47'),
(12287, 11495, 'test test test New comment', '1', '2009-02-24 13:50:33'),
(12288, 11496, 'Test new remark 1.', '1', '2009-02-24 13:56:29'),
(12289, 11497, 'Testing of the function of adding the remarks to the portal. I need to verify that the email is going through as programmed.', '1', '2009-02-24 14:08:31'),
(12290, 11498, 'Test escalation', '1', '2009-02-25 07:54:26'),
(12291, 11499, 'test', '1', '2009-02-25 08:09:15'),
(12292, 11500, 'test test', '1', '2009-02-25 10:34:57'),
(12293, 11501, 'Walked her through manually programming the pager. Tested and everything working great.', '1', '2009-02-25 10:49:17'),
(12297, 11506, 'test 1', '1', '2009-02-26 09:55:58'),
(12298, 11506, 'test 2', '1', '2009-02-26 09:56:25'),
(12299, 11507, 'test', '1', '2009-02-26 10:06:59'),
(12300, 11507, 'test', '1', '2009-02-26 10:07:02'),
(12301, 11508, 'Returned call. Explained why he has to change out the base unit.', '1', '2009-02-27 13:15:14'),
(12302, 11509, 'test 11509', '4', '2009-03-04 14:40:58'),
(12303, 11509, 'test', '1', '2009-03-13 07:45:24'),
(12304, 11510, 'test', '1', '2009-03-13 07:46:31'),
(12305, 11506, 'test', '1', '2009-03-13 07:56:33'),
(12306, 11506, 'test 2', '1', '2009-03-13 07:57:14'),
(12307, 11518, 'dafasdfad\r\ndfasf\r\ndf\r\nasdf\r\nasfd', '2', '2009-03-16 16:38:25'),
(12308, 11519, 'afasdfasdf<br />\r\nasfasdf<br />\r\nf<br />\r\nasdf<br />\r\nasdf<br />\r\n', '2', '2009-03-16 16:39:44');

 

That should do it.

 

 

[fenway]

Wait a second... there's an "OR" in the where clause!!!

[Drewser33]

Yeah??? I don't understand, if you replace the OR with an AND the query never results in any records matching.  I don't see how the OR is what is causing the problem.  Could you show the correct query and assist me by explaining why the OR is throwing it off?

 

Thanks

[fenway]

Because then only one of the conditions needs to be "in range".