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

    Unanswered: How to remove the records

    Code:
    SELECT Prsn.PersonID as HospitalID,
           Prsn.Name,
           Prsn.Type,
           Prsn.Sex,
           DateDiff(year,Prsn.BirthDate,GetDate()) as Age,
           IP.AttendingMD as Attending,
           IP.Location,
           IP.AdmitDt as AdmDate,
           DateDiff(day,IP.AdmitDt,GetDate())+1 as LOS,
           IP.Service,
           PTList.Status as Active
    FROM Person as Prsn
          INNER JOIN IPList as IP
          ON Prsn.PersonID = IP.PersonID
          LEFT JOIN PTList
          ON 
            Prsn.PersonID = PTList.PersonID 
            AND 
            PtList.ProviderCode='john'
    Order by Name

    The above will select all the records from InPatient and will retrieve either the status code ('A') or blank ('') from PTList, depending on whether or not the john has marked the record as "his".

    What I want to do is to return the same dataset, but WITHOUT any records belonging to john.

    I can't use <>'John' because 14 others might have the silly thing as active at the same time. What I'd really like to do is

    Select * from
    (the above)
    Where Status=Null

    I know it can be done, but I don't know how. Any ideas?
    DerFarm
    It IS as bad as you think
    and they ARE out to get you

  2. #2
    Join Date
    Mar 2003
    Location
    Indiana, USA
    Posts
    100
    I am guessing you want one of the following.

    Tim S

    SELECT Prsn.PersonID as HospitalID,
    Prsn.Name,
    Prsn.Type,
    Prsn.Sex,
    DateDiff(year,Prsn.BirthDate,GetDate()) as Age,
    IP.AttendingMD as Attending,
    IP.Location,
    IP.AdmitDt as AdmDate,
    DateDiff(day,IP.AdmitDt,GetDate())+1 as LOS,
    IP.Service,
    PTList.Status as Active
    FROM Person as Prsn
    INNER JOIN IPList as IP
    ON Prsn.PersonID = IP.PersonID
    LEFT JOIN PTList
    ON
    Prsn.PersonID = PTList.PersonID
    AND
    PtList.ProviderCode='john'
    WHERE PTList.Status IS NULL
    Order by Name


    Second Guess

    SELECT Prsn.PersonID as HospitalID,
    Prsn.Name,
    Prsn.Type,
    Prsn.Sex,
    DateDiff(year,Prsn.BirthDate,GetDate()) as Age,
    IP.AttendingMD as Attending,
    IP.Location,
    IP.AdmitDt as AdmDate,
    DateDiff(day,IP.AdmitDt,GetDate())+1 as LOS,
    IP.Service,
    PTList.Status as Active
    FROM Person as Prsn
    INNER JOIN IPList as IP
    ON Prsn.PersonID = IP.PersonID
    WHERE NOT EXISTS
    ( SELECT * FROM PTList WHERE
    Prsn.PersonID = PTList.PersonID
    AND
    PtList.ProviderCode='john'
    )
    Order by Name

  3. #3
    Join Date
    Nov 2002
    Posts
    19
    yup, it was the where not exists.

    I'd already tried a variant on the other one, it returned nothing, but I probably set it up incorrectly.

    Just tried the other way, also. Worked like a charm. I'm not positive, butg from somewhere I got the idea that the Exists works something like an IN statement? That would imply that the first way would be faster ... at least in machine terms.

    Thank you
    Last edited by DerFarm; 04-11-03 at 10:01.
    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
  •