SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question No Sub Queries - Now What

    I am moving an application from Access to MySQL. In the current application, I have a sub-query to return the employees who aren't on vaction that day.

    There is basically a table of vacation entries with the employee ID, a start day, and a stop day. There is also a table of employees. This is how it curently works -
    Code:
    Select Distinct EmployeeID, DepartmentID, CapacityHours 
    From Employees 
    Where Active = true 
    And CapacityHours > 0 
    And EmployeeID Not In (
    	  Select EmployeeID From Vacations 
    	  Where StartDate <= # & dtmToday.ToString("M/d/yyyy") & # 
    	  And EndDate >= #" & dtmToday.ToString("M/d/yyyy") & #
    );
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com

  2. #2
    One website at a time mmj's Avatar
    Join Date
    Feb 2001
    Location
    Melbourne Australia
    Posts
    6,282
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ah, yep this one can be done!

    First you need to work out the dates somehow when you build the query. Not knowing the language you're using, in the example below I've simply replaced the dates with whatever

    SELECT DISTINCT e.EmployeeID, e.DepartmentID, e.CapacityHours
    FROM
    Employees AS e
    LEFT JOIN Vacations AS v ON v.EmployeeID=e.EmployeeID AND v.StartDate <= whatever AND v.EndDate >= whatever
    WHERE
    e.Active = 1
    AND e.CapacityHours > 0
    AND v.EmployeeID IS NULL

    The "IS NULL" here does the same type of thing as a subquery with NOT EXISTS or NOT IN (depending on how it's worded).

    Note also that I've change Active = true to Active = 1.
    [mmj] My magic jigsaw
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    The Bit Depth Blog Twitter Contact me
    Neon Javascript Framework Jokes Android stuff

  3. #3
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the help. I did not realize you could have criteria within the Join clause before the Where statement.

    I did also notice the true/false when importing the Access database with Navicat. All of my boolean fields were converted into tinyint fields.

    Too bad I couldn't hold off until a stable release of v4.1. From what I read, it supports nested quireies and boolean data types.
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com


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
  •