Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002
    Posts
    19

    Unanswered: Need a double join with a where clause

    Code:
    
    SELECT Person.PersonID as HospitalID,
           Person.Name, ...
          IPList.AttendingMD as Attending,
          ...
          PTList.Status as Active
    FROM (
           Person INNER JOIN 
                        IPList ON 
                                 Person.PersonID = IPList.PersonID) 
                  LEFT JOIN (Select PTList.PersonID,PTList.Status from PTList
                         WHERE PtList.ProviderCode='John') ON PTList.PersonID=IPList.PersonID

    The Select after the Left Join gives an error, but when I join PTList and IPList on PersonID, the where clause gets left out in the main set. This causes me to get only the records where 'John' is the provider code.

    What I want is to get all the records of IPList inner Join with Person, and then I want the PTList.Active Status for Only those records in PTList where the ProviderCode='John'

    How can I get the where clause to hit ONLY on the PTList?
    DerFarm
    It IS as bad as you think
    and they ARE out to get you

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this:
    Code:
    SELECT Person.PersonID as HospitalID
         , Person.Name
         , ...
         . IPList.AttendingMD as Attending
         , ...
         , PTList.Status as Active
      FROM Person 
    INNER
      JOIN IPList
        ON Person.PersonID = IPList.PersonID
    LEFT
      JOIN PTList
        ON IPList.PersonID = PTList.PersonID
       AND                   PtList.ProviderCode='John'
    rudy
    http://r937.com/

  3. #3
    Join Date
    Nov 2002
    Posts
    19
    Perfecto. Thank you very much. The key is the word AND.

    Without it, the clause gets applied wrong.
    DerFarm
    It IS as bad as you think
    and they ARE out to get you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •