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

    Join Date
    Mar 2011
    Posts
    53
    Rep Power
    4

    Joins where 'row count' is nill


    Hi everyone

    I'm trying to use LEFT JOIN to identify which cars in a table have no associated drivers, by a specific date.

    The cars table contains:

    carID driverID

    The drivers table contains:

    driverID StartDate EndDate

    Code:
       SELECT cars.carID
            , drivers.driverID
            , drivers.StartDate
            , drivers.EndDate
         FROM cars
    LEFT JOIN drivers
           ON drivers.driverID = cars.driverID
        WHERE 'YYYY-MM-DD' NOT BETWEEN drivers.StartDate AND drivers.EndDate
    ... Returns all the driver bookings where the date I'm checking is not within the booking range. I appreciate that's expected behaviour from the query - but that's not what I want.

    What I want to find is all the cars.carID where there is NO possible driver booking; in other words, if 'YYYY-MM-DD' is not within any of the StartDate - EndDate booking ranges for that driverID, return the cars.carID.

    In psuedo code:

    Code:
       SELECT cars.carID
            , drivers.driverID
            , drivers.StartDate
            , drivers.EndDate
         FROM cars
    LEFT JOIN drivers
           ON drivers.driverID = cars.driverID
        WHERE "a search for 'YYYY-MM-DD' between drivers.StartDate 
              AND drivers.EndDate returned no results"
    Is that possible?

    Thanks
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2005
    Posts
    724
    Rep Power
    291
    Code:
    SELECT cars.carID
            , drivers.driverID
            , drivers.StartDate
            , drivers.EndDate
    FROM cars
    LEFT JOIN drivers ON drivers.driverID = cars.driverID
    WHERE drivers.StartDate IS NULL AND drivers.EndDate IS NULL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2011
    Posts
    53
    Rep Power
    4
    Thanks Vomster

    I probably didn't clearly explain what I'm trying to achieve!

    The query you propose will not return the unwanted rows - but I don't think it'll return rows where start and end dates do exist, but do not 'cover' the date I'm checking.

    For example, there could be 20 rows in the drivers table with start and end dates, but none with start and end dates that include the date I'm checking. In that case, I would want the relevant cars.carID returned.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    Code:
    SELECT cars.carID
      FROM cars
    LEFT OUTER
      JOIN drivers
        ON drivers.driverID = cars.driverID
       AND 'YYYY-MM-DD' BETWEEN drivers.StartDate AND drivers.EndDate
     WHERE drivers.driverID IS NULL
    the outer join's ON clause specifies what you ~don't~ want to find, so you write BETWEEN, not NOT BETWEEN, and then the WHERE clause ensures you didn't find it

    i removed the driver columns from the SELECT clause because you already know there isn't going to be anything in them

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2011
    Posts
    53
    Rep Power
    4
    Thank you - works perfectly!

IMN logo majestic logo threadwatch logo seochat tools logo