0

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?

1
  • You could use OUTER APPLY with TOP (or LEFT JOIN and GROUP BY). What I can't follow is why you have joined Company if you don't use it anywhere. And why Person_Last_Name and Person_First_Name need to converted to varchar, what are their datatypes? Oct 17, 2013 at 18:43

1 Answer 1

2

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
9
  • I need to list PImage_Path in the select list somehow so that I can bind it to the datagrid. I don't think this will do that for me.
    – Dan
    Oct 17, 2013 at 18:48
  • You mean with a column alias? (I just updated the code.)
    – db2
    Oct 17, 2013 at 18:49
  • This is a search query and a bunch of conditions are joined at the end dynamically, so trying to see if this will still work (I can always just add this at the end if not).
    – Dan
    Oct 17, 2013 at 18:54
  • I keep getting SYNTAX errors.
    – Dan
    Oct 17, 2013 at 19:02
  • hang on, I think I figured out the issue (my problem, not yours)
    – Dan
    Oct 17, 2013 at 19:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.