HELP!! Correlated Queries???

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

 

I dont know if I understand ok your problem. Tell me if this code is ok :

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)
and Actionlkp<>4
GROUP BY tbl_job_post_history.JobPostID

Code (markup):

Regards
Adrian

 

Here is are the tables... It kinda work.... i'll mes around with it some more..

-- 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);

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

 

I just double checked.... it does not work... Why did I choose such a difficult project for my first project....

 

With the query what I give you first time You get rid of Actionlkp 4

this is the result ... you see ?
+-----------+-----------+
| JobPostId | Actionlkp |
+-----------+-----------+
| 28 | 5 |
| 30 | 5 |
| 32 | 3 |
+-----------+-----------+

Regards
Adrian

P.S. Is not another type of error ?

 

When I tried that query in phpMyAdmin, it just repalced the actionlkp with another record of that JobPostID..

I understand how it is suppose to work.... I'll try again...

 

Here is what I get when I pull out all the posting with the latest action in the history table....



Now I want to not show the posting that is archived... (the Action id 4)



As you can see the record is replaced with the next most current action in the history table... I want that record not to show at all.. becasue the posting is still currently archived..

 

Ok ! Now I understand what you want
SELECT tbl_job_post_history.JobPostId,tbl_job_post_history.Actionlkp 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) and tbl_job_post_history.JobPostId not in (select distinct(JobPostId) from tbl_job_post_history where Actionlkp=4) GROUP BY tbl_job_post_history.JobPostID;

Tell me if is working

Regards
Adrian

 

Looks like thats got it... I'll get it into the page and let you know if there are any complications... I really appriciate the help.... I've spent almost 2 days trying to get that to work... I'll be releasing the BETA of my project hopefully April 3 2006, if not on that day then within a week after.. I'd like it if you messed around on the site and let me know what you think from a programmers point of view... Also, i'm sure i'll have lots of tweaking to do in the coming months.. I know there is better ways to write most of the code I have writtenso far.... It's just with deadlines!!!!!!!!!! I have to bust out something quick to please the boss... you know how it is...

www.wazzaweb.com

it's my project site.. The next big job board..

 

Good ideea with your board.

If you have problems with query ... I will track this thread ...

Regards

 

Found one problem... Ok, the SQL works with viewing job posts not archived.. It's great.. However, the employer has the choice to repost the job. In doing this the employer selects a POST NOW! link in the archived listing.. this will set a new post in the history table... So the SQL should check for the most current date in the history table... and if that entry is an archive then don't show it, if not then show it... With this SQL Statement, the job posting will never show if it has ever been archived..

I hope you can understand this.. If you need more details, let me know..

 

Try this for me is ok ...

SELECT t1.JobPostId,t1.Actionlkp FROM `tbl_job_post_history` as t1 JOIN `tbl_job_posting` ON tbl_job_posting.JobPostID = t1.JobPostID WHERE Date = (SELECT MAX(Date) FROM `tbl_job_posting` WHERE tbl_job_posting.JobPostID = t1.JobPostID) and t1.JobPostId not in (select distinct(JobPostId) from tbl_job_post_history where Actionlkp=4 and date=(select max(date) from tbl_job_post_history where JobPostId=t1.JobPostId) and JobPostID=t1.JobPostID) GROUP BY t1.JobPostID;

 

and on date field change in Date+Time ... because if a user is doing that in the same day ... you will have problems with query

Regards

 

I found a bug ... Is not geeting the last entrence from table anyway I optimised a bit ... try this:
SELECT t1.JobPostId,t1.Actionlkp,t1.Date FROM `tbl_job_post_history` as t1 join `tbl_job_posting` on tbl_job_posting.JobPostID = t1.JobPostID where t1.JobPostId not in (select distinct(JobPostId) from tbl_job_post_history where Actionlkp=4 and date=(select max(date) from tbl_job_post_history where JobPostId=t1.JobPostId) and JobPostID=t1.JobPostID) and HistID=(select max(HistID) from tbl_job_post_history where JobPostID=t1.JobPostId);

Regards

 

SELECT t1.JobPostId, t1.Actionlkp, t1.Date, tbl_job_posting . *
FROM `tbl_job_post_history` AS t1
JOIN `tbl_job_posting` ON tbl_job_posting.JobPostID = t1.JobPostID
WHERE t1.JobPostId NOT
IN (

SELECT DISTINCT (
JobPostId
)
FROM tbl_job_post_history
WHERE Actionlkp =4
AND date = (

SELECT MAX( date )
FROM tbl_job_post_history
WHERE JobPostId = t1.JobPostId
)
AND JobPostID = t1.JobPostID
)
AND HistID = (

SELECT MAX( HistID )
FROM tbl_job_post_history
WHERE JobPostID = t1.JobPostId
)
AND tbl_job_posting.UserID = '6';

I modified the SQL alittle... I'm still having problems... I archive a job posting... boom it goes to the archive list..., no longer shows up on the Job Post Search.. also, deletes any entries in a users Job Bank.. Well, I want to repost that job posting- so I click a link POST NOW!... if the employers has Post credits then boom, it takes a credit and puts a new entry as being posted.. now it's not in the archive list and show up in the job searches.. That part is not happening...

Ok, I just found something..... It does not work if the employers re-post the same day the job posting was archived... and now I cant get the achived job post out of the archived list...

It's driving me crazy

I changed the date field type to "DATETIME"... that will give the posting a time stamp as well... now it should work...

 

I told you to change the date field from history in datetime ... if you will do so will work

Regards
Adrian

 

IT WORKS!!!!!!!!!!! GREAT!!!!!!!!!!!!

Sorry, it was my fault the whole time.....

Now I need to reverse the SQL you made for the archive listing...

Thank you so much

 

Hi,

Probably when I will try to find a job I will subscribe at your site

Regards
Adrian

 

Maybe if the site goes well, I can give you a job...

I really appreciate the help... I have lots more statements to fiqure out...

I'll be back online later... Thanks again..