code duplication in sql case statements
- by NS
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!
