Catch multiple types of data in SQL Server

Question

I have a table (Task) like this:

Task Table

and I need answer like this:

TaskResult

I am doing the first query like this:

select 
    StudentID, AdmissionID, EnquiryID, EnquiryDetailsID
from  
    Task
where 
    TaskUser = 0 and BranchID = 1

• If I'm getting studentID then I create second query in loop for searching the student first name and last name.

• elseif I'm getting EnquiryID then I create second query in loop for searching the Enquiry first name and last name.

• elseif I'm getting AdmissionID then I create second query in loop for searching the Admission guys first name and last name.

• elseif I'm getting EnquiryDetailsID then I create second query in loop for searching the EnquiryDetails first name and last name.

So it creates loop in a loop and I get heavy load time on the page.

I need to combine both queries into one query. So page won't be loading.

I only have two elements i.e. taskUser and BranchID.

Please help me!! Thanks in advance !!!

Answer 1

So - it looks like you have an oddly organized task table, and as a result, you're going to have to do mildly weird things to query right. According to your description, a row in the task table contains either a studentId, an admissionId, an enquiryId, or an enquiryDetailId. This isn't an optimal way to do this...but I understand that sometimes you have to get by with what you have.

So, to get the names, you have to join to the source of the names...and assuming they're all over the place, in related tables, you could do something like:

select 
  t.StudentID,t.AdmissionID,t.EnquiryID,t.EnquiryDetailsID,x.FirstName,x.LastName
from Task t inner join Student s on t.StudentId = s.Id
union all
select 
  t.StudentID,t.AdmissionID,t.EnquiryID,t.EnquiryDetailsID,x.FirstName,x.LastName
from Task t inner join Admission a on t.AdmissionId = a.Id
union all 
select 
  t.StudentID,t.AdmissionID,t.EnquiryID,t.EnquiryDetailsID,x.FirstName,x.LastName
from Task t inner join Enquiry e on t.EnquiryId = e.Id
union all 
select 
  t.StudentID,t.AdmissionID,t.EnquiryID,t.EnquiryDetailsID,x.FirstName,x.LastName
from Task t inner join EnquiryDetail d on t.EnquiryDetailId = d.Id

...or, you can accomplish the same thing kinda inside-out:

select 
  t.StudentID, 
  t.AdmissionID, 
  t.EnquiryID, 
  t.EnquiryDetailsID,
  x.FirstName,
  x.LastName
from 
  Task t
  inner join
  (
    select 's' source, Id, FirstName, LastName from Student union all
    select 'a' source, Id, FirstName, LastName from Admission union all
    select 'e' source, Id, FirstName, LastName from Enquiry union all
    select 'd' source, Id, FirstName, LastName from EnquiryDetail
  ) as x
  on
    ( t.StudentId  = x.Id and x.source = 's' )
    or
    ( t.AdmissionId = x.Id and x.source = 'a' )
    or
    ( t.EnquiryId = x.Id and x.source = 'e' )
    or 
    ( t.EnquiryDetailId = x.Id and x.source = 'd' )
where 
  t.TaskUser=0 and t.BranchID=1

Answer 2

Use LEFT JOIN with COALESCE like this:

--not tested
select StudentID, AdmissionID, EnquiryID, EnquiryDetailsID, 
       COALESCE(s.name, e.name, d.name, ed.name) as name, etc.
from Task t
left join student s on s.id = t.studentID
left join Enquiry e on e.id = t.EnquiryID
left join Admission d on d.id = t.AdmissionID
left join EnquiryDetails ed on ed.id = t.EnquiryDetailsID
where TaskUser=0 and BranchID=1