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

    Join Date
    Jul 2014
    Posts
    34
    Rep Power
    4

    SQL Inner Joins Help again


    Hi All,

    I'm sorry but again I'm struggling to get the SQL script below to generate the results I'd expect. There is one result I would expect to be returned, but nothing is being returned.

    Code:
    SELECT e.Forenames, e.Surname, h.Horse_ID , h.HorseName , eh.EmployeeHorse_ID , eh.StartDate , eh.EndDate , eh.EmployeeRole FROM tblHorse AS h 
    INNER JOIN tblEmployee_Horse AS eh ON eh.Horse_ID = h.Horse_ID 
    INNER JOIN tblEmployee AS e ON e.Employee_ID = eh.Employee_ID
    WHERE e.Employee_ID = 18;
    Basically, I've got a table of employees, a table of horses, and a 3rd table adjoining them, detailing which employees are currently loaning horses.

    the Employee ID 18 should return one record from the joining table.

    Any assistance would be most appreciated.

    Many Thanks,
    Graham
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    Originally Posted by Graham_K
    the Employee ID 18 should return one record from the joining table.
    " should "

    what does this actually return?
    Code:
    SELECT COUNT(*)
      FROM tblEmployee_Horse 
     WHERE Employee_ID = 18
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2014
    Posts
    34
    Rep Power
    4
    That query returns a column count of 2, as, on review, both booked out horses are to employee 18.....
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    so what was wrong with your original query again?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2014
    Posts
    34
    Rep Power
    4
    My original query wasn't returning any results, where I'd be expecting to see those 2 records.....
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    look at your data

    i think your query is fine

    no results means some join fields don't contain what you think is there
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo