SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 30 of 30
  1. #26
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you changed the query -- the SELECT and GROUP BY no longer agree

    make sure the GROUP BY has exactly the same columns as the SELECT, and see if that fixes it

    i know the query used to work -- i tested it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  2. #27
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry to keep nagging but no matter what I do I just can't get this query to work correctly. I am doing what you mentioned in the last post and grouping by all selected items.

    Please take a moment to look over, I have attached exactly how the database tables look at this state of time.

    Here is my query

    $sql = "
    select d.id
    , t1.id
    , t1.vac_date_added
    , t1.vac_reference
    , t1.vac_title
    from deployment_role as d
    inner
    join deployment_vacancy as t1
    on t1.role_id = d.id
    inner
    join deployment_vacancy as t2
    on t1.role_id = t2.role_id
    and t1.vac_date_added <= t2.vac_date_added
    where d.id in (1,2,3,4,5)
    group
    by d.id
    , t1.id
    , t1.vac_date_added
    , t1.vac_reference
    , t1.vac_title
    having count(*) <= 2
    ";

    And all I keep getting back is record 119, please refer to the screenshot to see which one this is.

    I just can't sus it out.

    Please help!
    Attached Images Attached Images

  3. #28
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    query looks okay, so i decided to test it (again) using your data from post #25

    that's when i noticed that you have defined vac_date_added as VARCHAR(100)

    you're never going to get accurate date calculations with that, sorry

    in fact, almost all your columns are VARCHAR(100), even the numeric ids

    better reconsider

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #29
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ar, so is it because the date fields are not datetime types?

  5. #30
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    that would explain it not working correctly, yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •