Can someone tell why this query does not work for the given tables (see below)?
It should return results for records having ONLY expired date(s) AND current date(s)
select JPosts2.JID
, State.StateTerm
, JPosts2.JState
from JPosts2
inner
join State
on State.StateAbbr = JPosts2.JState
inner
join JRun as expired
on expired.FK_JobID = JPosts2.JID
and expired.EndRun < current_date
inner
join JRun as current
on current.FK_JobID = JPosts2.JID
and current.StartRun <= current_date
and current.EndRun >= current_date
where JPosts2.JClass` = 'Mechanics'
group
by JPosts2.JID
, State.StateTerm
, JPosts2.JState
-- Table structure for table JPosts2
CREATE TABLE JPosts2 (
JID varchar(10) NOT NULL default '',
JClass varchar(40) NOT NULL default '',
JState char(3) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- Dumping data for table JPosts2
INSERT INTO JPosts2 VALUES ('100', 'Mechanics', 'PA');
INSERT INTO JPosts2 VALUES ('200', 'Mechanics', 'KY');
INSERT INTO JPosts2 VALUES ('300', 'Mechanics', 'MO');
INSERT INTO JPosts2 VALUES ('400', 'Florists', 'MO');
INSERT INTO JPosts2 VALUES ('500', 'Mechanics', 'KY');
INSERT INTO JPosts2 VALUES ('600', 'Hairstylists', 'MO');
INSERT INTO JPosts2 VALUES ('700', 'Mechanics', 'MO');
INSERT INTO JPosts2 VALUES ('800', 'Mechanics', 'MO');
INSERT INTO JPosts2 VALUES ('900', 'Mechanics', 'PA');
INSERT INTO JPosts2 VALUES ('1000', 'Mechanics', 'IL');
INSERT INTO JPosts2 VALUES ('1100', 'Mechanics', 'IL');
--
-- Table structure for table State
CREATE TABLE State (
StateID int(5) unsigned NOT NULL auto_increment,
StateAbbr varchar(4) NOT NULL default '',
StateTerm varchar(15) NOT NULL default '',
PRIMARY KEY (StateID)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=52 ;
-- Dumping data for table State
INSERT INTO State VALUES (14, 'IL', 'Illinois');
INSERT INTO State VALUES (18, 'KY', 'Kentucky');
INSERT INTO State VALUES (26, 'MO', 'Missouri');
INSERT INTO State VALUES (39, 'PA', 'Pennsylvania');
--
-- Table structure for table JRun
CREATE TABLE JRun (
JRID varchar(5) NOT NULL default '',
FK_JobID varchar(5) NOT NULL default '',
StartRun date NOT NULL default '0000-00-00',
EndRun date NOT NULL default '0000-00-00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- Dumping data for table JRun
INSERT INTO JRun VALUES ('10', '100', '2006-06-27', '2006-07-27');
INSERT INTO JRun VALUES ('20', '200', '2006-01-27', '2006-02-27');
INSERT INTO JRun VALUES ('30', '300', '2005-04-07', '2005-05-07');
INSERT INTO JRun VALUES ('40', '400', '2005-04-07', '2005-05-07');
INSERT INTO JRun VALUES ('50', '500', '2006-08-23', '2006-09-23');
INSERT INTO JRun VALUES ('60', '600', '2006-03-27', '2006-04-27');
INSERT INTO JRun VALUES ('70', '700', '2006-03-08', '2006-04-08');
INSERT INTO JRun VALUES ('80', '800', '2006-05-08', '2006-06-08');
INSERT INTO JRun VALUES ('90', '900', '2006-08-23', '2006-09-23');
INSERT INTO JRun VALUES ('100', '1000', '2006-08-24', '2006-09-24');
INSERT INTO JRun VALUES ('110', '1100', '2006-08-24', '2006-09-24');
[B]notes:[/B] problem seems to be associated with possible overlap on the two date checks (date checks look right logically?):
current.EndRun >= current_date // with expired.EndRun < current_date
[I]removing[/I]:
current.EndRun >= current_date and there are results
