I'm working on a job board and I need to figure out this SQL Statement. I have a table with Job Postings and also a Job Posting history table. Any time a job posting is created, edited or archived, then any entry is add to the history table. The query I need is that when the user sees his main menu the job postings are sepparted by active postings and archived posting.

How can I seperate them..?

bit more detail and we may be able to figure it out

but a join sounds favourite

something like

SELECT table1.fieldlist, table2.fieldlist from table1, table2 INNER JOIN table2 ON table2.field = table1.field WHERE table1.field = criteria ;

Ok, I'll try to break it down further...

Table one is the Job Posting info: Job desription, Job Title, stuff like that...

Table 2 is the Job Posting History Table... Anytime anything is done with a job posting, an entry is made..

For example:

I post a job....

Table 2 - gets userID, jobpostID, Action (posted), date (Feb 26)

I edit a job posting....

Table 2 - gets userID, jobpostID, Action (modified), date (Mar 12)

My posting is view....

Table 2 - gets userID, jobpostID, Action (viewed), date (Mar 12)

I archive my posting or the 30days is up and the posting get archived....

Table 2 - gets userID, jobpostID, Action (archive), date (Mar 12)

Now here's where i'm having trouble.. On the Employers Main Menu, I show all Active Postings (ones still viewable by Job Seekers) and archived posting (not viewable but can br reposted).. When I archive a psoting it still shows up in the active...

I'm sure there is a simple way to write this... I'm just pressed by my boss to finish this by the end of the week and I've got so much let to do.. My mind is being fried..

Thanks in advance

so to recap, just to make sure;

table2 is basically an audit trail and when it is marked as archived you want the job post to drop off the active list.

if so, it's simpler to have a flag in the main database for active and set that to false when the post is archived

Yes, I was thinking to make a seperate table call tbl_job_post_archive... So if the Job Post is archived, then it will insert the info into the history and also set a record with the archive date in the archive table.

So when I view the postings then I can just check if it has a record in the archive table... Also if the Employers, deciceds to reactivate that job posting, then the archive record for that job posting in the archive table is deleted and a post is set in the (what you refered to as) the Trail Table.

I think this way will work, but maybe it's just the easy way out... I know there must be a way to reference only the one table...

More details at: http://www.webmaster-talk.com/the-da...d-queries.html