I'm trying to write this query and it killing me... I need a query to pull some data out of a history table... I want to show a list of job postings that have not been archived... when a job post is created any entry is put in the history the the job post was post.. when the job posting is viewed, another entry in the history that the posting was viewed.. Once the posting is archived, another entry is entered.

Now why I do a search for job posting I don't want to show the archived posts...???? I can figure out how to pull this off...

Here is what I have so far:

SELECT *
FROM `tbl_job_post_history`
JOIN `tbl_job_posting`
ON tbl_job_posting.JobPostID = tbl_job_post_history.JobPostID
WHERE Date =
(SELECT MAX(Date)
FROM `tbl_job_posting`
WHERE tbl_job_posting.JobPostID = tbl_job_post_history.JobPostID)
GROUP BY tbl_job_post_history.JobPostID

Here is the results I get:



The record with the Actionlkp of 4.... I want to get rid of that.. That Job Posting is archived at the moment...

WHERE ... AND Actionlkp <> 4 ....

Well, that doesn't work.... There is multiple records and the current SQL displays this.. When I and that Actionlkp <> 4 then the record that has the for is replace with a record containing a date prior to the 4.... I need that whole record not to display..

29 | 106 | 6 | 5 | 2006-03-29 Something like this..... It just shows the record with the next highest date..

Here is the SQL to create the 2 tables i'm working on... This is really driving me crazy...

Thanks in advance

-- Table structure for table `tbl_job_post_history`
--

CREATE TABLE `tbl_job_post_history` (
`JobPostID` bigint(20) unsigned NOT NULL,
`HistID` bigint(20) unsigned NOT NULL auto_increment,
`UserID` bigint(20) unsigned NOT NULL,
`Actionlkp` bigint(20) unsigned NOT NULL,
`Date` date NOT NULL,
PRIMARY KEY (`HistID`),
KEY `JobPostID` (`JobPostID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=111 ;

--
-- Dumping data for table `tbl_job_post_history`
--

INSERT INTO `tbl_job_post_history` VALUES (31, 110, 7, 4, '2006-03-30');
INSERT INTO `tbl_job_post_history` VALUES (31, 109, 4, 3, '2006-03-30');
INSERT INTO `tbl_job_post_history` VALUES (29, 108, 6, 4, '2006-03-30');
INSERT INTO `tbl_job_post_history` VALUES (30, 103, 4, 5, '2006-03-30');
INSERT INTO `tbl_job_post_history` VALUES (29, 102, 4, 5, '2006-03-30');
INSERT INTO `tbl_job_post_history` VALUES (30, 101, 4, 5, '2006-03-15');
INSERT INTO `tbl_job_post_history` VALUES (29, 100, 4, 5, '2006-03-30');
INSERT INTO `tbl_job_post_history` VALUES (30, 99, 4, 5, '2006-03-30');
INSERT INTO `tbl_job_post_history` VALUES (31, 98, 4, 5, '2006-03-30');
INSERT INTO `tbl_job_post_history` VALUES (29, 97, 0, 3, '2006-03-30');
INSERT INTO `tbl_job_post_history` VALUES (32, 96, 4, 3, '2006-03-30');
INSERT INTO `tbl_job_post_history` VALUES (28, 95, 4, 5, '2006-03-30');
INSERT INTO `tbl_job_post_history` VALUES (28, 94, 4, 3, '2006-03-30');
INSERT INTO `tbl_job_post_history` VALUES (32, 107, 6, 1, '2006-03-30');
INSERT INTO `tbl_job_post_history` VALUES (30, 92, 6, 3, '2006-03-29');
INSERT INTO `tbl_job_post_history` VALUES (30, 91, 4, 5, '2006-03-29');
INSERT INTO `tbl_job_post_history` VALUES (32, 90, 6, 1, '2006-03-29');
INSERT INTO `tbl_job_post_history` VALUES (29, 89, 4, 3, '2006-03-29');
INSERT INTO `tbl_job_post_history` VALUES (30, 88, 4, 3, '2006-03-29');
INSERT INTO `tbl_job_post_history` VALUES (32, 106, 6, 4, '2006-03-30');
INSERT INTO `tbl_job_post_history` VALUES (31, 86, 7, 1, '2006-03-29');
INSERT INTO `tbl_job_post_history` VALUES (29, 85, 7, 3, '2006-03-29');
INSERT INTO `tbl_job_post_history` VALUES (30, 84, 7, 1, '2006-03-29');
INSERT INTO `tbl_job_post_history` VALUES (29, 82, 6, 1, '2006-03-29');
INSERT INTO `tbl_job_post_history` VALUES (28, 81, 6, 1, '2006-03-29');

-- --------------------------------------------------------
-- Table structure for table `tbl_job_posting`
--

CREATE TABLE `tbl_job_posting` (
`UserID` bigint(20) unsigned NOT NULL,
`JobPostID` bigint(20) unsigned NOT NULL auto_increment,
`JobTitle` varchar(255) NOT NULL,
`Salarylkp` tinyint(3) unsigned NOT NULL,
`JobDescription` mediumtext NOT NULL,
`EmploymentType` tinyint(3) unsigned NOT NULL,
`EmploymentExpType` tinyint(3) unsigned NOT NULL,
`EducationExpType` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`JobPostID`),
KEY `UserID` (`UserID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=33 ;

--
-- Dumping data for table `tbl_job_posting`
--

INSERT INTO `tbl_job_posting` VALUES (7, 30, 'Vice President of Business Development', 1, 'Medium sized company that is growing quickly! Opportunity with this company at this point and this level are incredible! Licensed and bonded in 50 states to do collections. Perks that come with this are company car, expense account, computer, cell phone and top notch benefits. \r\n\r\nPrior Collections Industry experience is REQUIRED. We would prefer to work with candidates who have current experience calling on accounts that are "prime" accounts in Financials, Credit, Banks, Auto Loans, and Medical.', 1, 2, 2);
INSERT INTO `tbl_job_posting` VALUES (6, 28, 'Service Representative - IWMP/ATA Operations', 1, 'Premier Books Direct provides a Shop-At-Work service for Schools, Nursing Homes, Day Care Centers, Medical Facilities and other Businesses. This well received service allows employees the opportunity to purchase hundreds of brand name books, toys, electronics and gift items from high profile companies such as Disney, Universal Studios, Better Homes & Gardens, Samsonite, Crayola, NFL and many more at 40 – 70% less than retail stores.', 1, 2, 2);
INSERT INTO `tbl_job_posting` VALUES (6, 29, 'Business driven individuals wanted!!!!', 1, 'This responsibility includes single channel and multi-channel radios, computers and video systems with a specialization in VTC Technology. Provides concept of operations development, configuration management, test and evaluation, integration and installation, information, assurance, acquisition, integrated logistics, and life cycle support for the systems. Includes requirements analysis of existing and future technology.', 2, 2, 2);
INSERT INTO `tbl_job_posting` VALUES (6, 32, 'Electrical Designer (Aerospace)', 1, 'Responsible for generating and checking electrical drawings (i.e. Wiring Diagrams, Harness Assembly, Routing and Clipping) for modifications to commercial/corporate/private aircraft. Provide technical leadership and electrical designs on assigned programs. Responsible for providing technical support and guidance to Associate Designers. \r\n\r\nPOSITION DETAILS – Will work together with engineers to accomplish detailed electrical design of aircraft equipment installations and alterations (antennas, avionics equipment, interior components and furnishings). Prepare and/or check documentation as necessary to satisfy customer, company and regulatory requirements. Work to quickly resolve electrical manufacturing and installation problems.', 3, 2, 2);

-- --------------------------------------------------------

what database are you using ?

MySQL

version number.

because that query will not work in some versions.

MySQL 5.0