Using If else Or Case from More Table In SQL Server

Question

I need to select all from Log Table and import data when Case

If UserGrade =1 this is Admin User and I need to select Admin Name by Log.User

If UserGrade =2 this is Teacher User and I need to select Teacher Name by Log.User

If UserGrade =3 this is Student User and I need to select student Name by Log.User

I try This

select L.[UserID],L.[UserGrade],L.[Operation],L.[OpDate],

case 

when  L.[UserGrade]=2 then (select teacherNAME from Teacher ,[Log] where teacherID=[Log].[UserGrade] )

when  L.[UserGrade]=1 then (select [Adm_Name] from Administrator ,[Log] where [Adm_ID]=[Log].[UserGrade] )

when  L.[UserGrade]=3 then (select [studentNAME] from student ,[Log] where[studentID]=[Log].[UserGrade] )

end
from [Log] L ,Teacher , Administrator  ,student

Answer 1

Evaluates a list of conditions and returns one of multiple possible result expressions.

The CASE expression has two formats:

• The simple CASE expression compares an expression to a set of simple expressions to determine the result.

• The searched CASE expression evaluates a set of Boolean expressions to determine the result. Both formats support an optional ELSE argument.

CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.

You should check this Link

Answer 2

This is the fine-tuned query of yours. Tables with comma is the old style, avoid those and use JOIN. Also use ELSE in the CASE

SELECT L.[UserID], L.[UserGrade], L.[Operation], L.[OpDate],
    CASE L.[UserGrade]
    WHEN 2 THEN T.[teacherNAME] 
    WHEN 1 THEN A.[Adm_Name]
    WHEN 3 THEN S.[studentNAME]
    ELSE '' END [Name]
FROM [Log] L 
LEFT JOIN Teacher T ON T.[teacherID] = L.[UserGrade]
LEFT JOIN Administrator A ON A.[Adm_ID] = L.[UserGrade]
LEFT JOIN student S ON S.[studentID] = L.[UserGrade]