sql help

[dianaj59]

I have an sql statement I will post below.

It returns:

JohnDoe1
JohnDoe2	09/27/2010
JohnDoe2
JohnDoe3
JohnDoe4
JohnDoe5
JohnDoe6	09/30/2010
JohnDoe6
JohnDoe7

Where the NAME is duplicated, I only want to see the name WITH a date.

However, I DO still want the names with NO date that are NOT duplicates.

I've looked at this too long, I can not see how to do it.

Thanks.

Code:

SELECT DISTINCT       CLIENT_DEMOGRAPHIC."FIRST_NAME"||' '||CLIENT_DEMOGRAPHIC."LAST_NAME" AS NAME,       case WHEN VACCINE_ID IN (21,23,79,127,130)              AND IMMUN."VACCINATION_DATE" BETWEEN to_date('2010/08/01','yyyy/mm/dd')                                                AND to_date('2010/10/20','yyyy/mm/dd')            THEN max(IMMUN."VACCINATION_DATE")               else Null       end AS MaxDate         FROM     "IR"."REPORT_LIST" REPORT_LIST         LEFT OUTER JOIN "IR"."REPORT_LIST_CLIENT" REPORT_LIST_CLIENT         ON REPORT_LIST."REPORT_LIST_ID" =  REPORT_LIST_CLIENT."REPORT_LIST_ID"            LEFT OUTER JOIN "DM"."CLIENT_DEMOGRAPHIC" CLIENT_DEMOGRAPHIC            ON REPORT_LIST_CLIENT."CLIENT_ID" = CLIENT_DEMOGRAPHIC."CLIENT_ID"               LEFT OUTER JOIN "DM"."IMMUN_FACT" IMMUN               ON CLIENT_DEMOGRAPHIC."CLIENT_ID" = IMMUN."CLIENT_ID"                  LEFT OUTER JOIN "DM"."VACCINE" VACCINE_A                  ON IMMUN."VACCINE_ID" = VACCINE_A."VACCINE_ID"     WHERE REPORT_LIST_NAME = 'mdhIt' GROUP BY      CLIENT_DEMOGRAPHIC."FIRST_NAME"||' '||CLIENT_DEMOGRAPHIC."LAST_NAME",      IMMUN."VACCINATION_DATE",      VACCINE_ID            ORDER BY      Name,MaxDate  

[mdahlman]

Normally this is best handled with a subquery. Something like this:

SELECT
  client.name, max_immun.vac_date
FROM
  client
  left outer join ( select max(vac_date) from immun group by client_id ) max_immune on ( max_immune.client_id = client.client_id )

You could put that subquery into a view if it's something you use in other places.

Regards,
Matt
 

[dianaj59]

Here is my current FROM statement:

FROM
/* return all REPORT_LIST and (corresponding REPORT_LIST_CLIENT records or null)*/
     "IR"."REPORT_LIST" REPORT_LIST
        LEFT OUTER JOIN "IR"."REPORT_LIST_CLIENT" REPORT_LIST_CLIENT
        ON REPORT_LIST."REPORT_LIST_ID" = REPORT_LIST_CLIENT."REPORT_LIST_ID"
 
/* return any CLIENT record that correspond to above REPORT_LIST_CLIENT (or null)*/
           LEFT OUTER JOIN "DM_OWNER"."CLIENT" CLIENT
           ON REPORT_LIST_CLIENT."CLIENT_ID" = CLIENT."CLIENT_ID"
 
/* return any IMMUN_FACT records that correspond to above CLIENT_DEMOGRAPHIC (or null)*/
              LEFT OUTER JOIN "DM"."IMMUN" IMMUN
              ON CLIENT."CLIENT_ID" = IMMUN."CLIENT_ID"
 
/* return any VACCINE_A records that correspond to above IMMUN_FACT (or null)*/
                 LEFT OUTER JOIN "DM"."VACCINE" VACCINE_A
                 ON IMMUN."VACCINE_ID" = VACCINE_A."VACCINE_ID"
 

Where do I place:

    left outer join
    ( select max(IMMUN."VACCINATION_DATE")
    from DM.IMMUN group by client_id ) maxImmun
     on (IMMUN."CLIENT_ID" = CLIENT."CLIENT_ID")

I tried placing it at the end of the FROM statement, but the query ran on for at least 15 minutes, so I killed it. 

 

[dianaj59]

Okay...I see how it is...Your subselect works fine.

THANKS.