HELP!! Correlated Queries???

Ok, I see you learn quickly ... so maybe you will not need me !

Regards

 

Ok, i need some adjustments...

<cfquery datasource="#datasource#" name="get_job_postings">
SELECT t1.JobPostId,t1.Actionlkp,t1.Date, tbl_job_posting.*, tbl_employer_info.*, tblkp_employment_type.*, tblkp_education_exp_lvl.*, tblkp_salary_range.*, tblkp_employment_exp_lvl.*
FROM `tbl_job_post_history` as t1
JOIN `tbl_job_posting`
ON tbl_job_posting.JobPostID = t1.JobPostID
JOIN `tbl_employer_info`
ON tbl_employer_info.UserID = tbl_job_posting.UserID
JOIN `tblkp_employment_type`
ON tblkp_employment_type.ID = tbl_job_posting.EmploymentType
JOIN `tblkp_education_exp_lvl`
ON tblkp_education_exp_lvl.ID = tbl_job_posting.EducationExpType
JOIN `tblkp_salary_range`
ON tblkp_salary_range.ID = tbl_job_posting.Salarylkp
JOIN `tblkp_employment_exp_lvl`
ON tblkp_employment_exp_lvl.ID = tbl_job_posting.EmploymentExpType
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 Actionlkp = 1)
</cfquery>

Now I need to only show the records having a posted date greater than or equal to Now()-30, 30 days ago.

Also, if you know how to do it, if a record is past the 30 day mark i want to automaticaly archive that posting..

This one seems tough right?

 

Hi
1.

SELECT t1.JobPostId,t1.Actionlkp,t1.Date, tbl_job_posting.*, tbl_employer_info.*, tblkp_employment_type.*, tblkp_education_exp_lvl.*, tblkp_salary_range.*, tblkp_employment_exp_lvl.*
FROM `tbl_job_post_history` as t1
JOIN `tbl_job_posting`
ON tbl_job_posting.JobPostID = t1.JobPostID
JOIN `tbl_employer_info`
ON tbl_employer_info.UserID = tbl_job_posting.UserID
JOIN `tblkp_employment_type`
ON tblkp_employment_type.ID = tbl_job_posting.EmploymentType
JOIN `tblkp_education_exp_lvl`
ON tblkp_education_exp_lvl.ID = tbl_job_posting.EducationExpType
JOIN `tblkp_salary_range`
ON tblkp_salary_range.ID = tbl_job_posting.Salarylkp
JOIN `tblkp_employment_exp_lvl`
ON tblkp_employment_exp_lvl.ID = tbl_job_posting.EmploymentExpType
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 Actionlkp = 1)
and date>(curdate()+ interval 30 day)

Code (markup):

2. what means archive ? just update the last actionlckp to 4 ? or you need a new insert in database ?

Regards

 

Archiving a Job Post just inserts a record into the history table with an Actionlkp of 4 (archive)... History tables are all inserts for statistics purposes..

When a job seeker searches the database, each time they perform a search it will check the job posting results for any posts over the 30 day limit. If a Job post exceeds the 30 day limit then a record is inserted in the history as archived..

 

SELECT t1.JobPostId, t1.Actionlkp, t1.Date,
tbl_job_posting. * ,
tbl_job_post_requirements. * ,
tbl_employer_info. * ,
tblkp_employment_type. * ,
tblkp_education_exp_lvl. * ,
tblkp_salary_range. * ,
tblkp_employment_exp_lvl. * ,
tbl_job_post_location. * ,
tbl_job_post_job_categories.JobCat,
tblkp_countries. *

FROM `tbl_job_post_history` AS t1

JOIN `tbl_job_post_location`
ON tbl_job_post_location.JobPostID = t1.JobPostID

JOIN `tbl_job_post_requirements`
ON tbl_job_post_requirements.JobPostID = t1.JobPostID

LEFT JOIN `tbl_job_post_job_categories`
ON tbl_job_post_job_categories.JobPostID = t1.JobPostID

JOIN `tblkp_countries`
ON tbl_job_post_location.CC1 = tblkp_countries.CC1

JOIN `tbl_job_posting`
ON tbl_job_posting.JobPostID = t1.JobPostID

JOIN `tbl_employer_info`
ON tbl_employer_info.UserID = tbl_job_posting.UserID

JOIN `tblkp_employment_type`
ON tblkp_employment_type.ID = tbl_job_posting.EmploymentType

JOIN `tblkp_education_exp_lvl`
ON tblkp_education_exp_lvl.ID = tbl_job_posting.EducationExpType

JOIN `tblkp_salary_range`
ON tblkp_salary_range.ID = tbl_job_posting.Salarylkp

JOIN `tblkp_employment_exp_lvl`
ON tblkp_employment_exp_lvl.ID = tbl_job_posting.EmploymentExpType

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 Actionlkp =1
)
AND date >(curdate() - interval 30 day)

AND tbl_job_posting.JobTitle LIKE '%admin%'
OR tbl_job_posting.JobDescription LIKE '%admin%'
OR tbl_job_post_requirements.JobRequirements LIKE '%admin%'
ORDER BY Date DESC

This Query is showing 1 record twice... One with an actionlkp of 3 and One with an actionlkp of 1.. same info just different actionlkp..

This is the keyword search function... The red section is only run if the user enters a keyword...

 

use distinct ... it's showing twice because of your condition

[COLOR="Red"]AND tbl_job_posting.JobTitle LIKE '%admin%'
OR tbl_job_posting.JobDescription LIKE '%admin%'
OR tbl_job_post_requirements.JobRequirements LIKE '%admin%'[/COLOR]

Code (markup):

try to use distinct

SELECT distinct(t1.JobPostId), etc etc

Regards
Adrian

 

<cfif session.keyword neq "">
AND t1.Actionlkp = 1 AND tbl_job_posting.JobTitle LIKE '%#session.Keyword#%'
OR t1.Actionlkp = 1 AND tbl_job_posting.JobDescription LIKE '%#session.Keyword#%'
OR t1.Actionlkp = 1 AND tbl_job_post_requirements.JobRequirements LIKE '%#session.Keyword#%'
<cfset session.pgv = "detailed">
</cfif>

I tried the select distinct but it was not working... This seem to have done the trick... Do you see any potential problems with this?

 

try this:

AND t1.Actionlkp = 1 AND (tbl_job_posting.JobTitle LIKE '%#session.Keyword#%'
OR tbl_job_posting.JobDescription LIKE '%#session.Keyword#%'
OR tbl_job_post_requirements.JobRequirements LIKE '%#session.Keyword#%')

I think need to work

Regards

 

that worked...

here is the site so far... let me know what you think... I guess I should also post this in the Website Review section as well... Some things still don't work yet.. And there are no job on it yet either.. Gotta start somewhere you know..

http://www.wazzaweb.com

 

I want to be the first with the review, so :

Is nice and clean, but will be more than that after the beta period ... no ?

Regards

 

? I don't understand what you mean...?

Will there be more what? Are you asking?

 

If your asking will there be more then what I have now, then yes of course..

I want to keep it clean without a bunch of crazy ads.. there will be some but only on certain pages..

I wanted to go for a cross between Google and Yahoo.... Google is very simple, to the point and easy to use... Yahoo is colorful and provides a solid layout.. I think I acomplished that.. Its pretty solid.. I have alot of work to do on the guts but thats were I need everyone help.. I'm looking for peoples opinions on what the average people would like out of a job board.. I want it to be functional on all levels.. small companies to large corporations...

Remember, Google's BETA didn't look so great at first.. They started small then just kept going.. Now they's taken over the world..

 

Success !

Regards

 

Ok, looks like that SQL Statements we've been working on the past few days works great on my machine... For some reason now that is loaded onto the hosting server its not working... The same SQL gives results on my computer but not on the server.. ??? I'm confused

 

Check what mysql version they have on the server

Regards