SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2004
    Location
    St. Paul
    Posts
    61
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Join with Where Clause Question

    SELECT *
    FROM TimeRecords T
    LEFT JOIN Items ON T.ItemID = I.ItemID
    LEFT JOIN ItemType IT ON I.ItemTypeID = IT.ItemTypeID
    LEFT JOIN Service S ON T.UserID = S.UserID
    LEFT JOIN Companies on T.CompanyID = Companies.CompanyID
    LEFT JOIN CompanyEmp on Companies.CompanyID = CompanyEmp.CompanyID
    LEFT JOIN Contract on Companies.AgreementID = Contract.AgreementID

    Here's the query I have right now. There will be multiple Company Contact Records and I want only the records that have CompanyEmp.EmpType = 1. How would I go about this?

    Thanks for your help.
    Ryan

  2. #2
    SitePoint Wizard rbutler's Avatar
    Join Date
    Jul 2003
    Location
    Springfield, MO
    Posts
    1,867
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    W/out knowing how this db is setup I'm sort of guessing here, but could something like this work?

    Code:
    SELECT *
    FROM TimeRecords T
    LEFT JOIN Items ON T.ItemID = I.ItemID
    LEFT JOIN ItemType IT ON I.ItemTypeID = IT.ItemTypeID
    LEFT JOIN Service S ON T.UserID = S.UserID
    LEFT JOIN Companies on T.CompanyID = Companies.CompanyID
    LEFT JOIN CompanyEmp on Companies.CompanyID = CompanyEmp.CompanyID
    LEFT JOIN Contract on Companies.AgreementID = Contract.AgreementID
    WHERE  CompanyEmp.EmpType = 1
    Not sure why you're left joining either (probably a reason, I just don't know ), usually you would only do this in rare circumstances. In the practicaly world, I've only ran into this scenerio once and I've designed many db since that time. Hope it gives a little insight.
    Ryan Butler

    Midwest Web Design

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    left outer joins are required whenever you want all the rows from the left table, whether or not they have matching rows from the right table

    if you are looking for specific rows in one of the right tables, you have to put that condition into the ON clause, otherwise, if you put it into the WHERE clause, you are effectively turning the left outer join into an inner join

    so it would be like this:
    Code:
    select *
      from TimeRecords T
    left outer 
      join Items as I
        on T.ItemID = I.ItemID
    left outer 
      join ItemType IT 
        on I.ItemTypeID = IT.ItemTypeID
    left outer 
      join Service S 
        on T.UserID = S.UserID
    left outer 
      join Companies 
        on T.CompanyID = Companies.CompanyID
    left outer 
      join CompanyEmp 
        on Companies.CompanyID = CompanyEmp.CompanyID
       and CompanyEmp.EmpType = 1
    left outer 
      join Contract 
        on Companies.AgreementID = Contract.AgreementID
    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
  •