CODEWITHSUNDEEP
sql
Frankie
Frankie

Reputation: 2265

SQL join help? Only show results where a child table contains 1 or more records

I'm looking to only show records where a row exists in a child table.

I have two tables. I only want to return records from IP_OP that contain at least 1 record in CD_REVIEWS. I can't figure out how to accomplish this.

SELECT 
    IP_OP.PATIENT_CTRL_NUM,
    IP_OP.ADMIT_HOUR,
    CD.REVIEW_DATE  
FROM
    IP_OP_PATIENT_DET IP_OP,
    CD_REVIEWS CD
WHERE

Both tables contain PATIENT_CTRL_NUM.

Upvotes: 0

Views: 82

Answers (2)

Abe Miessler
Abe Miessler

Reputation: 85056

It depends on which review_date you want ot select, but something like this should work:

SELECT     IP_OP.PATIENT_CTRL_NUM,
           IP_OP.ADMIT_HOUR,
           MAX(CD.REVIEW_DATE)  
FROM       IP_OP_PATIENT_DET IP_OP
INNER JOIN CD_REVIEWS CD
on         IP_OP.PATIENT_CTRL_NUM = CD.PATIENT_CTRL_NUM --or whatever column you need to join on
WHERE  .....
GROUP BY IP_OP.PATIENT_CTRL_NUM, IP_OP.ADMIT_HOUR

If you are fine with returning multiple IP_OP_PATIENT_DET records then you could lose the MAX and GROUP BY

Upvotes: 2

thezboe
thezboe

Reputation: 552

SELECT 
    IP_OP.PATIENT_CTRL_NUM,
    IP_OP.ADMIT_HOUR,
    CD.REVIEW_DATE  
FROM
    IP_OP_PATIENT_DET IP_OP INNER JOIN CD_REVIEWS CD
    ON IP_OP.PATIENT_CTRL_NUM = CD.PATIENT_CTRL_NUM

Upvotes: 0

Related Questions