What Type of Join to Use in this Search? I have the following query that is bound to a datagrid:

select 
  distinct  a.Person_ID, 
            a.Person_Last_Name, 
            a.Person_DOB, 
            CASE 
              WHEN a.Person_Last_Name IS NULL 
                THEN '' 
              ELSE CONVERT(varchar, a.Person_Last_Name) 
            END 
            + ', ' + 
            CASE 
              WHEN a.Person_First_Name IS NULL 
                THEN '' 
              ELSE CONVERT(varchar, a.Person_First_Name) 
            END 
            + ' ' + 
            coalesce(a.Person_Suffix,'') 
            + ' ' + 
            coalesce(a.Person_Middle_Name,'') 
            + 
            CASE 
              WHEN a.Person_Archived='T' 
                THEN ' (Archived)' 
              ELSE '' 
            END 
            + 
            CASE 
              WHEN a.Person_Deceased='T' 
                THEN ' (DECEASED)' 
              ELSE '' 
            END AS PersonName 
FROM PERSON a 
  LEFT OUTER JOIN COMPANY c 
    ON a.Person_Company_ID = c.Company_ID


I need to also pull up an image for each person when one is available (but display 'None' when no image is available). There are potentially multiple images for each Person - I only want to select the first one. However, there are also possibly no images on file, in which case a default value should be populated. Here is the current SQL statement to fetch images for Persons:

SELECT 
  PImage_Path 
FROM PIMAGE 
  WHERE PImage_Subject_ID=@Subject_ID


When no images are available, however, I would like it to return 'None' as the value of PImage_Path and not restrict the original select statement from finding Persons with no image. What type of join should I use? How would this query look?
Probably something along these lines, courtesy of OUTER APPLY:

select 
    --A bunch of stuff
    ISNULL(pic.PImage_Path, 'None') AS PImage_Path
FROM PERSON a 
  LEFT OUTER JOIN COMPANY c
    ON a.Person_Company_ID = c.Company_ID
  OUTER APPLY (
    SELECT TOP 1
      PImage_Path
    FROM PIMAGE 
    WHERE PIMAGE.PImage_Subject_ID = a.Person_ID --Fix this if I'm making the wrong assumption about your key columns.
    ORDER BY ... --Something sensible to determine which picture will be used
  ) pic