1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

HELP!! Correlated Queries???

Discussion in 'MySQL' started by Nakirema, Mar 29, 2006.

  1. #1
    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...
     
    Nakirema, Mar 29, 2006 IP
  2. Nakirema

    Nakirema Peon

    Messages:
    113
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #2
    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:

    [​IMG]

    The record with the Actionlkp of 4.... I want to get rid of that.. That Job Posting is archived at the moment...
     
    Nakirema, Mar 29, 2006 IP
  3. sacx13

    sacx13 Active Member

    Messages:
    438
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    58
    #3
    I dont know if I understand ok your problem. Tell me if this code is ok :

    Code (Text):
    1. SELECT *
    2. FROM `tbl_job_post_history`
    3. JOIN `tbl_job_posting`
    4. ON tbl_job_posting.JobPostID = tbl_job_post_history.JobPostID
    5. WHERE Date =
    6. (SELECT MAX(Date)
    7. FROM `tbl_job_posting`
    8. WHERE tbl_job_posting.JobPostID = tbl_job_post_history.JobPostID)
    9. and Actionlkp<>4
    10. GROUP BY tbl_job_post_history.JobPostID
    11.  
    Regards
    Adrian
     
    sacx13, Mar 29, 2006 IP
  4. Nakirema

    Nakirema Peon

    Messages:
    113
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #4
    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);

    -- --------------------------------------------------------
     
    Nakirema, Mar 30, 2006 IP
  5. Nakirema

    Nakirema Peon

    Messages:
    113
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #5
    I just double checked.... it does not work... :( Why did I choose such a difficult project for my first project.... :confused:
     
    Nakirema, Mar 30, 2006 IP
  6. sacx13

    sacx13 Active Member

    Messages:
    438
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    58
    #6
    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 ?
     
    sacx13, Mar 30, 2006 IP
    dct likes this.
  7. Nakirema

    Nakirema Peon

    Messages:
    113
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #7
    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...
     
    Nakirema, Mar 30, 2006 IP
  8. Nakirema

    Nakirema Peon

    Messages:
    113
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #8
    Here is what I get when I pull out all the posting with the latest action in the history table....

    [​IMG]

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

    [​IMG]

    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..
     
    Nakirema, Mar 30, 2006 IP
  9. sacx13

    sacx13 Active Member

    Messages:
    438
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    58
    #9
    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
     
    sacx13, Mar 30, 2006 IP
  10. Nakirema

    Nakirema Peon

    Messages:
    113
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #10
    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..
     
    Nakirema, Mar 30, 2006 IP
  11. sacx13

    sacx13 Active Member

    Messages:
    438
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    58
    #11
    Good ideea with your board.

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

    Regards
     
    sacx13, Mar 30, 2006 IP
  12. Nakirema

    Nakirema Peon

    Messages:
    113
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #12
    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..
     
    Nakirema, Mar 30, 2006 IP
  13. sacx13

    sacx13 Active Member

    Messages:
    438
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    58
    #13
    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;
     
    sacx13, Mar 30, 2006 IP
  14. sacx13

    sacx13 Active Member

    Messages:
    438
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    58
    #14
    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
     
    sacx13, Mar 30, 2006 IP
  15. sacx13

    sacx13 Active Member

    Messages:
    438
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    58
    #15
    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
     
    sacx13, Mar 31, 2006 IP
  16. Nakirema

    Nakirema Peon

    Messages:
    113
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #16
    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...
     
    Nakirema, Mar 31, 2006 IP
  17. sacx13

    sacx13 Active Member

    Messages:
    438
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    58
    #17
    I told you to change the date field from history in datetime ... if you will do so will work

    Regards
    Adrian
     
    sacx13, Mar 31, 2006 IP
  18. Nakirema

    Nakirema Peon

    Messages:
    113
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #18
    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
     
    Nakirema, Mar 31, 2006 IP
  19. sacx13

    sacx13 Active Member

    Messages:
    438
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    58
    #19
    Hi,

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

    Regards
    Adrian
     
    sacx13, Mar 31, 2006 IP
  20. Nakirema

    Nakirema Peon

    Messages:
    113
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #20
    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..
     
    Nakirema, Mar 31, 2006 IP