CODEWITHSUNDEEP
mysqlsql
anon_swe
anon_swe

Reputation: 9355

SQL: Case When After Multiple Left Joins

I'm using MySQL with a table called students. I'm trying to add a column that indicates whether a student came from the US, Europe, South America, or none of those. Specifically, I'd like to put a 0 in the column if none of those are true, 1 if US, 2 if Europe, 3 if South America.

So I'm doing something like this:

SELECT name,
CASE WHEN ... 
FROM original_table or
LEFT JOIN us_table us
ON or.student_id = us.student_id
LEFT JOIN europe_table eur
ON or.student_id = eur.student_id
LEFT JOIN south_america_table sa
ON or.student_id = sa.student_id

I'm having trouble with the CASE WHEN part. I could just say something like "when the id column from the US table is not null and the other id columns (from Europe and South America) are null, record a 1.

Similarly, if the id column from the Europe table isn't null and the US and South America table's id columns are null, record a 2.

Is there a better way?

Upvotes: 0

Views: 201

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270783

You have a pretty bad data structure, I think. Generally, splitting up equivalent information (such as geography) into different tables is not a good data model.

I would simply use exists:

SELECT name,
       (CASE WHEN EXISTS (SELECT 1 FROM us_table us WHERE ot.student_id = us.student_id)
             THEN 1
             WHEN EXISTS (SELECT 1 FROM europe_table eur WHERE ot.student_id = eur.student_id)
             THEN 2
             WHEN EXISTS (SELECT 1 FROM south_america_table sa WHERE ot.student_id = sa.student_id)
             THEN 3
             ELSE 0
        END) as origin
FROM original_table ot;

I also changed the table alias to ot from or, because or is a reserved word.

Upvotes: 0

Barmar
Barmar

Reputation: 782166

Testing each against NULL seems like the best way. Use ELSE for the 0 value.

CASE WHEN us.student_id IS NOT NULL THEN 1
     WHEN eur.student_id IS NOT NULL THEN 2
     WHEN sa.student_id IS NOT NULL THEN 3
     ELSE 0
END

Upvotes: 1

Related Questions