Query Optimization 1

[TheWebDataGuy]

I am looking for some suggestions to optimize the query below. Currently it takes till eternity to display the output and the users won't like it.

The reason is quite obvious: I have used subqueries in loop.
There are three tables involved Max, Employee and Departments. Max table has fields EmployeeID1, EmployeeID2....EmployeeID5 and I need to retrieve the Employee Names & other information for all of them from Employee table. Some kind of a JOIN would be faster but I couldn't think of it.


SELECT [Max].[LogID], [Max].[DecisionDate], [Departments].[DepartmentName],(SELECT Employee.FirstName FROM Employee WHERE Max.EmployeeID1 = Employee.StaffID) AS FirstName1, (SELECT Employee.LastName FROM Employee WHERE Max.EmployeeID1 = Employee.StaffID) AS LastName1, (SELECT Employee.FirstName FROM Employee WHERE Max.EmployeeID2 = Employee.StaffID) AS FirstName2, (SELECT Employee.LastName FROM Employee WHERE Max.EmployeeID2 = Employee.StaffID) AS LastName2

FROM Max, Departments
WHERE [Max].[DeptCode]=[Departments].[DeptCode]


Thanks!

 

[rac2]

Does this work?

SELECT [Max].[LogID], [Max].[DecisionDate], [Departments].[DepartmentName], a.FirstName, a.LastName, b.FirstName, b.LastName

FROM Max
JOIN Departments ON [Max].[DeptCode]=[Departments].[DeptCode]
JOIN (SELECT * FROM Employee WHERE Max.EmployeeID1 = Employee.StaffID) AS a
  ON Max.EmployeeID1 = Employee.StaffID
JOIN (SELECT * FROM Employee WHERE Max.EmployeeID2 = Employee.StaffID) AS b
  ON Max.EmployeeID2 = Employee.StaffID

 

[tlbroadbent]

Try joining the Employees table rather than doing sub-queries. You'll need to JOIN it twice. If there is a chance that no matching record exists, you will want to make one or more of the queries an OUTER JOIN.

SELECT
m.LogID, m.DecisionDate,
d.DepartmentName,
e1.FirstName AS FirstName1,
e1.LastName AS LastName1,
e2.FirstName AS FirstName2,
e2.LastName AS LastName2

FROM (((Departments As d
INNER JOIN [Max] As m
ON d.DeptCode=m.DeptCode)
INNER JOIN Employees As e1
ON m.EmployeeID1 = e1.StaffID)
INNER JOIN Employees As e2
ON m.EmployeeID1 = e2.StaffID)

I recommend avoiding the use of reserved words such as MAX as object or column names. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[TheWebDataGuy]

Thanks Terry, it worked fine. I had to use two Outer Joins.

I understand why MAX should not be used as a Table or Column Name. That was a silly mistake on my part.