(SQL) In a query, how do I use a CASE statement that would only
conditionally require a JOIN?
I have a main data table that contains data or a code that denotes an
error case when data is missing. I need to: 1) Display the data if it's
valid or 2) If it's not valid (it's an error code), display a
human-readable version of that error code
The human-readable form of the error code comes from another table that
I'm joining with to translate the code.
Here's some example data from the main table:
Table pub_k12.schools
school_name | title1 | school_wide_title1 | total_students
-------------+--------+--------------------+---------------
School-A | M | M | 2300
School-B | N | N | -2
School-C | M | N | -1
The code translation table looks like this:
Table pub_k12.ref_school_field_data
data_code | data_string
----------+-------------
-1 | No Data
-2 | N/A
-9 | Bad Data
M | No Data
N | N/A
Unfortunately, this data format cannot be changed. This is what I'm given.
Here's the query I'm using:
SELECT stype.type_string, d1.data_string AS title1, d2.data_string AS
school_title1,
CASE
WHEN s.total_students::int < 0 THEN d3.data_string
ELSE s.total_students
END "total_students"
FROM pub_k12.schools AS s
JOIN pub_k12.ref_school_type AS stype ON s.school_type = stype.type_code
JOIN pub_k12.ref_school_field_data AS d1 ON s.title1 = d1.data_code
JOIN pub_k12.ref_school_field_data AS d2 ON s.school_wide_title1 =
d2.data_code
JOIN pub_k12.ref_school_field_data AS d3 ON s.total_students = d3.data_code;
(Ignore the stype for now - it's a similar translate-code-to-readable
situation)
So I'm checking that total_students is >0, in which case I'm displaying it
outright. Otherwise, I do the JOIN to translate the code. Here's what I
get when I run the query:
school_name | title1 | school_title1 | total_students
-------------+---------+---------------+---------------
School-B | N/A | N/A | N/A
School-C | No Data | N/A | No Data
So as you can see, School-A with valid "total_students" data isn't
returned. The query works as long as total_students is an error code, but
if it's valid data (like for School-A), then the last JOIN on d3 doesn't
work because that number (2300 in this case) doesn't match up with any
value in the the error code table.
Is there a way to tell it to only do that join if that CASE-WHEN condition
is met?
Thanks!
No comments:
Post a Comment