Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2012
    Posts
    5

    Unanswered: IDs without a code in another table

    Hi all,
    New to the forum so please be gentle.
    I need to extract patients that do not have a specific code within a joined table.
    I’ve been working on this all day!

    Rows of observations (defined by a code) several for each patient. Patient and Observation table are joined by PID.
    Code 2323 in the observations table states they’ve had a colonoscopy.
    I need all patients that do NOT have this code in the observation table. Using NULL won’t work since this ObCode will not be present and won’t have a value.
    I’ve tried ‘IN’ and ‘EXIST’ but they return all of the observations (codes) that don’t comply with 2323. I’ve attempted subqueries and using ‘distinct’ but still no luck.

    Example:

    Ex. 1
    Person Table:
    PID: 111
    Name: John Smith

    Observation Table:
    PID: 111
    ObCode: 3278

    PID: 111
    ObCode: 444

    [I want the PID returned once but not the rows]

    Ex. 2
    Person Table:
    PID: 222
    Name: Bob Smith

    Observation Table:
    PID: 222
    ObCode: 3278

    PID: 111
    ObCode: 2323

    [I don’t want this PID returned]

    SQL:
    SELECT p.lastname, p.firstname, p.dateofbirth,o.obsdate, o.obsvalue
    from person p, obs o
    where p.pid = o.pid
    and NOT EXISTS (select distinct pid
    from obs
    where hdid = 2323)

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT p.lastname, 
           p.firstname, 
           p.dateofbirth, 
           o.obsdate, 
           o.obsvalue 
    FROM   person p, 
           obs o 
    WHERE  p.pid = o.pid 
           AND p.pid IN (SELECT pid 
                         FROM   obs 
                         MINUS 
                         SELECT pid 
                         FROM   obs 
                         WHERE  hdid = 2323);
    Last edited by anacedent; 09-13-12 at 20:40. Reason: formatted the SQL
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2012
    Posts
    5
    Hi Anacedent,
    Thanks!
    This still doesn’t provide what I need as it returns all rows without the code and I just need the PIDs that do not have an instance of code=2323.

    SQL:
    SELECT p.lastname,
    p.firstname,
    p.dateofbirth,
    o.obsdate,
    o.obsvalue
    FROM person p,
    obs o
    WHERE p.pid = o.pid
    AND p.lastname LIKE 'ZZZ%'
    AND p.pid IN (SELECT pid
    FROM obs
    MINUS
    SELECT pid
    FROM obs
    WHERE hdid = 2323);

    Results:
    ZZZBIRD BIG 14-APR-69 13-FEB-12 Denies fever, chills, sweats,
    ZZZBIRD BIG 14-APR-69 23-NOV-09 Nziavake Masimasi, MD
    ZZZBIRD BIG 14-APR-69 23-NOV-09 CERVIX
    ZZZBIRD BIG 14-APR-69 23-NOV-09 FINAL
    ZZZBIRD BIG 14-APR-69 23-NOV-09 CERVIX
    ZZZBIRD BIG 14-APR-69 23-NOV-09 FINAL
    ZZZBIRD BIG 14-APR-69 23-NOV-09 CERVIX

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SELECT pid
    FROM obs
    MINUS
    SELECT pid
    FROM obs
    WHERE hdid = 2323;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Sep 2012
    Posts
    5
    Thanks again but still no luck.

    SQL:
    SELECT p.lastname,
    p.firstname,
    p.dateofbirth,
    o.obsdate,
    o.obsvalue
    FROM person p,
    obs o
    WHERE p.pid = o.pid
    AND p.lastname LIKE 'ZZZ%'
    AND p.pid IN (SELECT pid
    FROM obs
    MINUS
    SELECT pid
    FROM obs
    WHERE hdid = 2323)

    Results:
    ZZZO'REILLY MOLLY 20-APR-64 04-JUN-10 no
    ZZZO'REILLY MOLLY 20-APR-64 04-JUN-10 no
    ZZZO'REILLY MOLLY 20-APR-64 04-JUN-10 no
    ZZZO'REILLY MOLLY 20-APR-64 04-JUN-10 no
    ZZZO'REILLY MOLLY 20-APR-64 04-JUN-10 no

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    what is result set that you expect/desire?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Sep 2012
    Posts
    5
    Just the patients that do not have an entry in the obs table with the code 2323.

  8. #8
    Join Date
    Sep 2012
    Posts
    5
    2323 is a colonoscopy. I want to know those that have never had one.

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I never had it.

    Anyone else?

    How about
    Code:
    select pid 
      from person
    minus
    select pid
      from obs
      where code = 2323

Posting Permissions

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