Nicky's Tech Blog

Here is the situation I meet nowadays:

This is a very simple database, only including three tables: The students table is the main table. And if a student is a good student, there will be records in StudentsGoods table. Similarly, if a student is a bad student, there will be records in StudentsBads table. I want to show the whole table of students and there is another column in the table to indicate whether he is a good student or not.

Here we need to find out whether there is record in the specific table and decide which data to show. We use the CASE statement in SQL:

SELECT s.Id, [Test] =
CASE 
    WHEN EXISTS (SELECT b.Id FROM dbo.StudentBads b WHERE b.Id = s.Id) THEN 'Bad'
    WHEN EXISTS (SELECT g.Id FROM dbo.StudentGoods g WHERE g.Id = s.Id) THEN 'Good'
    ELSE 'Empty'
END
FROM dbo.Students s

Then we can get the result:

The CASE statement is so handy that it solves lots of problem.