code duplication in sql case statements

Question

Hi I'm trying to output something like the following but am finding that there is a lot of code duplication going on.

| australian_has_itch | kiwi_has_itch |
| yes                 | no            |
| no                  | n/a           |
| n/a                 | no            |

...

My query looks like this with two case statements that do the same thing but flip the country (my real query has 5 of these case statements):

SELECT 
  CASE
    WHEN
      NOT EXISTS (
        SELECT person_id
        FROM people_with_skin 
        WHERE people_with_skin.person_id = people.person_id
        AND people.country = "Australia"
      ) 
      THEN 'N/A'
    WHEN
      EXISTS (
        SELECT person_id
        FROM itch_none_to_report
        WHERE people.country = "Australia"
        AND person_id = people.person_id
      )
      THEN 'None to report'
    WHEN
      EXISTS (
        SELECT person_id
        FROM itchy_people
        WHERE people.country = "Australia"
        AND person_id = people.person_id
      )
      THEN 'Yes'
    ELSE 'No'
  END australian_has_itch,

  CASE
    WHEN
      NOT EXISTS (
        SELECT person_id
        FROM people_with_skin 
        WHERE people_with_skin.person_id = people.person_id
        AND people.country = "NZ"
      ) 
      THEN 'N/A'
    WHEN
      EXISTS (
        SELECT person_id
        FROM itch_none_to_report
        WHERE people.country = "NZ"
        AND person_id = people.person_id
      )
      THEN 'None to report'
    WHEN
      EXISTS (
        SELECT person_id
        FROM itchy_people
        WHERE people.country = "NZ"
        AND person_id = people.person_id
      )
      THEN 'Yes'
    ELSE 'No'
  END kiwi_has_itch,
FROM people

Is there a way for me to condense this somehow and not have so much code duplication?

Thanks!

Answer 1

Use:

   SELECT CASE
            WHEN x.personid IS NOT NULL AND x.country = 'Australia' THEN 'N/A'
            WHEN y.personid IS NOT NULL AND y.country = 'Australia' THEN 'None to report'
            ELSE 'No'
          END AS australian_has_itch,
          CASE
            WHEN x.personid IS NOT NULL AND x.country = 'NZ' THEN 'N/A'
            WHEN y.personid IS NOT NULL AND y.country = 'NZ' THEN 'None to report'
            ELSE 'No'
          END AS australian_has_itch
     FROM PEOPLE p
LEFT JOIN (SELECT DISTINCT
                  pws.person_id,
                  p.country
             FROM people_with_skin pws
             JOIN PEOPLE p ON p.person_id = pws.person_id) x ON x.person_id = p.person_id
LEFT JOIN (SELECT DISTINCT
                  intr.person_id, 
                  p.country
             FROM itch_none_to_report intr
             JOIN PEOPLE p ON p.personid = intr.personid) y ON y.person_id = p.person_id