#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    79
    Rep Power
    2

    Last result and next two


    I am trying to get a return from my data that will give me the last pick that was entered. And then the next two that are due to pick.

    I am able to get the last pick easy enough:

    Code:
    SELECT v.pick_id, v.employee_id, e.name
    FROM vacation_picks as v
    LEFT OUTER JOIN
    employee as e ON
    v.employee_id = e.employee_id
    WHERE v.platoon_id = 1
    ORDER BY v.pick_id DESC
    LIMIT 1
    Its getting the next two that is stumping me (thats not hard). The thing is, they may or may not have entries in the vacation_picks table. Also they pick in order of employee_id.
    I tried employee_id +1 but that can give me a result of an employee_id that may not exist, or someone on a different platoon.

    Any suggestions?

    Thank you
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,746
    Rep Power
    4288
    i would like you to look at your own query and analyze it critically --
    Code:
    FROM vacation_picks as v
    LEFT OUTER JOIN
    employee as e ON
    v.employee_id = e.employee_id
    this LEFT OUTER JOIN is written in a way that rows from `v` will be returned **even if** there is no matching `e` row

    please explain under what circumstances you will have a vacation pick for an employee that doesn't exist
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    79
    Rep Power
    2
    Thank you for pointing that out. Inner join should be what I am to use here?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,746
    Rep Power
    4288
    Originally Posted by SGC3
    Inner join should be what I am to use here?
    come on, man, i know you can do this

    inner join requires both sides

    how does that square with "they may or may not have entries in the vacation_picks table"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    79
    Rep Power
    2
    Your right. Thanks for the vote of confidence.

    I never can seem to keep how this works in my head. But when you get me talking through it I figure it out...sometimes.

    So I need a join that has all of the entries from table 'e' and regardless of matching entries in table 'v'. So if I have my join as v.employee_id = e.employee_id would be a RIGHT JOIN?
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,746
    Rep Power
    4288
    Originally Posted by SGC3
    So if I have my join as v.employee_id = e.employee_id would be a RIGHT JOIN?
    not necessarily

    it is the same join condition for INNER, LEFT, RIGHT, or FULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    79
    Rep Power
    2
    What determines which you are calling left and right then? I am mistaken again
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,746
    Rep Power
    4288
    please google "what is the difference between left outer join and right outer join"

    by the way, INNER JOIN and LEFT OUTER JOIN are really the only ones you need for the time being

    FULL JOIN is exceedingly rare, and CROSS JOIN has a pretty specific purpose

    let me give you a hint, you should never need RIGHT OUTER JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    79
    Rep Power
    2
    lol doesnt leave much to decide from.

    LEFT OUTER JOIN "show me all students, with their corresponding locker if they have one"

    I want to see all of the employees, and their corresponding vacation days, if they have one
  18. #10
  19. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,126
    Rep Power
    4103
    Originally Posted by SGC3
    What determines which you are calling left and right then? I am mistaken again
    Have a read here: When is a Left table 'Left' and a right table 'Right'


    Generally, in my experience you almost always want either INNER or LEFT joins, almost never RIGHT, CROSS, or FULL.


    You need to think about what order you list your tables in though to keep things as either inner or left joins. If you think you need a right join, you probably just have your tables backwards. I like to think about things as starting with some base list of information, then pulling in whatever supplemental information is needed.


    In your example, your base list of information is your employee list, so that's your starting point.
    Code:
    SELECT e.employee_id
    FROM employee e

    For each employee, you want to know what vacation picks that employee has made, so add that data source


    Code:
    SELECT e.employee_id
    FROM employee e
    INNER JOIN vacation_picks v ON v.employee_id=e.employee_id

    If still having the employee in the results even if their vacation pick count is zero is important, then you need to LEFT JOIN instead
    Code:
    SELECT e.employee_id
    FROM employee e
    LEFT JOIN vacation_picks v ON v.employee_id=e.employee_id

    You're only concerned with a certain platoon, so you want to filter by that. If you're using a LEFT JOIN, then that filter needs to be part of the ON condition. If you put it as part of the WHERE clause then your LEFT JOIN becomes an INNER JOIN
    Code:
    SELECT e.employee_id
    FROM employee e
    LEFT JOIN vacation_picks v ON v.employee_id=e.employee_id AND v.platoon_id=1

    Finally, you want the most recent pick first in the list, so apply an ordering. Ideally you should order by some sort of DATETIME/TIMESTAMP field rather than the records ID#.
    Code:
    SELECT e.employee_id
    FROM employee e
    LEFT JOIN vacation_picks v ON v.employee_id=e.employee_id AND v.platoon_id=1
    ORDER BY 
        v.pick_id DESC



    Now I'm not really sure what you want from this point on. Sounds like maybe you want the next two employees with no vacation pick by order of employee id? Getting that would be another query where you look for employee_id's > the last pick's employee_id (from the above query) where no pick has been made.


    But then what happens when everyone has made a pick? No more picks, or does it cycle back around to the first employee?
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    79
    Rep Power
    2

    Now I'm not really sure what you want from this point on. Sounds like maybe you want the next two employees with no vacation pick by order of employee id? Getting that would be another query where you look for employee_id's > the last pick's employee_id (from the above query) where no pick has been made.


    But then what happens when everyone has made a pick? No more picks, or does it cycle back around to the first employee?

    Yes, the next two employees, but they could have picks or not. It often goes through the cycle several times. When they are out of 'weeks' the cant pick anymore.

    Thanks so much for both or your help. Its very educational
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    79
    Rep Power
    2
    I came up with this.

    The problem is its giving me the last pick taken, and then the next. I am after the next two in this case.

    Code:
    SELECT name FROM employee
    WHERE employee_id > (SELECT e.employee_id
    FROM employee e
    LEFT JOIN vacation_picks v ON v.employee_id=e.employee_id AND e.employee_id = 2
    ORDER BY 
        v.pick_id DESC
    LIMIT 1) AND employee.retired IS NULL AND employee.platoon_id = 2
    ORDER BY employee.employee_id ASC
    LIMIT 2
  24. #13
  25. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,126
    Rep Power
    4103
    Your query is definitely incorrect, due to this:

    LEFT JOIN vacation_picks v ON v.employee_id=e.employee_id AND e.employee_id = 2
    I don't know anything about your table structure or your data or what process you're trying to implement, so that makes recommending any particular query pretty much impossible. Your query looks ok for the most part based on what I think I understand. The inner query should find the most recent pick's employee id and then you'd get a list of employee's with ID's higher than that. You're not checking if those employee's have any vacation picks, but it sounds like they shouldn't based on your process description.

    This query is mostly the same as your query above, but with a different inner query.
    Code:
    SELECT e.name
    FROM employee e
    WHERE
        e.employee_id > (
            SELECT v.employee_id
            FROM vacation_picks v
            WHERE
                v.platoon_id=2
            ORDER BY
                v.pick_id DESC
            LIMIT 1
        )
        AND e.retired IS NULL
        AND e.platoon_id = 2
    ORDER BY
        e.employee_id ASC
    If all you need for your inner query is the employee_id of the most recent vacation pick, then there's no need to involve the employee table at all. Just order your vacation picks to put the most recent one first, and grab that row's employee_id column.

    If you need more help, you'll probably have to post more details about your data and exactly what kind of process you're trying to implement because as of right now, to me at least, it makes no sense. A SQLFiddle setup would be nice.
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  26. #14
  27. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,746
    Rep Power
    4288
    Originally Posted by SGC3
    I am trying to get a return from my data that will give me the last pick that was entered. And then the next two that are due to pick.
    please note that "last pick entered" pretty much means INNER, not LEFT OUTER

    now for the tough part -- how do you define "next" ???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo