CODEWITHSUNDEEP
sqldb2
user1873736
user1873736

Reputation: 101

DB2 SQL nested queries

I need to list the first name and last name of the youngest patient that is prescribed penicillin, using nested queries. Here is what I have

SELECT P.FIRST_NAME, P.LAST_NAME, MIN(P.AGE) AS AGE
FROM PATIENT AS P
WHERE P.PATIENT_NO IN(
    SELECT PRE.PATIENT_NO
    FROM PRESCRIPTION AS PRE
    WHERE MEDICATION_CODE IN (
            SELECT M.MEDICATION_CODE
            FROM MEDICATION AS M
            WHERE MEDICATION_DESC = 'Penicillin'))
GROUP BY P.FIRST_NAME, P.LAST_NAME
HAVING MIN(P.AGE);

The results come back with the two people that are prescribed penicillin, not just the youngest.

Upvotes: 0

Views: 2793

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

Why do you have to use nested queries?

In any case, the having clause is not doing what you want. You can get what you want by doing this:

SELECT P.FIRST_NAME, P.LAST_NAME, MAX(P.AGE) as AGE
FROM PATIENT AS P
WHERE P.PATIENT_NO IN (
    SELECT PRE.PATIENT_NO
    FROM PRESCRIPTION AS PRE
    WHERE MEDICATION_CODE IN (
            SELECT M.MEDICATION_CODE
            FROM MEDICATION AS M
            WHERE MEDICATION_DESC = 'Penicillin'
                             )
   )
GROUP BY P.FIRST_NAME, P.LAST_NAME
ORDER BY MAX(P.AGE)
FETCH FIRST 1 ROWS ONLY;

EDIT:

Actually, I agree with FloChanz, and the group by is probably not necessary (unless you need to remove duplicates):

SELECT P.FIRST_NAME, P.LAST_NAME, P.AGE
FROM PATIENT AS P
WHERE P.PATIENT_NO IN (
    SELECT PRE.PATIENT_NO
    FROM PRESCRIPTION AS PRE
    WHERE MEDICATION_CODE IN (
            SELECT M.MEDICATION_CODE
            FROM MEDICATION AS M
            WHERE MEDICATION_DESC = 'Penicillin'
                             )
   )
ORDER BY P.AGE
FETCH FIRST 1 ROWS ONLY;

Upvotes: 2

user1873736
user1873736

Reputation: 101

Figured it out. Thanks for all the suggestions

SELECT P.FIRST_NAME, P.LAST_NAME
 FROM PATIENT AS P
WHERE P.AGE IN (
 SELECT MIN(P.AGE)
 FROM PATIENT AS P
 WHERE  P.PATIENT_NO IN(
SELECT PRE.PATIENT_NO
FROM PRESCRIPTION AS PRE
WHERE MEDICATION_CODE IN (
        SELECT M.MEDICATION_CODE
        FROM MEDICATION AS M
        WHERE MEDICATION_DESC = 'Penicillin')));

Upvotes: 1

FloChanz
FloChanz

Reputation: 3429

I guess that some inner join might be better like this : 

SELECT P.FIRST_NAME, P.LAST_NAME, P.AGE
FROM MEDICATION AS M 
INNER JOIN PRESCRIPTION AS PRE on PRE.MEDICATION_CODE = M.MEDICATION_CODE
INNER JOIN PATIENT AS P on P.PATIENT_NO = PRE.PATIENT_NO
WHERE M.MEDICATION_DESC = 'Penicillin'
ORDER BY P.AGE 
FETCH FIRST 1 ROWS ONLY;

Upvotes: 1

Related Questions