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
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]