Simplify Query SQL-CodePudding

I have a query like:

--FIRST QUERY
SELECT      a.EmployeeId, a.Seq, a.EmployeeAddress
INTO        #Address
FROM        Address a
WHERE       a.Seq = 1

--SECOND QUERY
SELECT      a.EmployeeId, a.PayrollType, SUM(a.Amount) Amount
INTO        #Payroll
FROM        Payroll a
WHERE       a.PayrollType = 'THR'
GROUP BY    a.EmployeeId, a.PayrollType

--RESULT
SELECT      a.EmployeeId, a.EmployeeName, b.ReligionName, 
            ISNULL(c.EmployeeAddress, '-') EmployeeAddress, ISNULL(d.Amount,0) Amount
FROM        Employee a
            INNER JOIN Religion b ON a.ReligionId = b.ReligionId
            LEFT JOIN #Address c ON a.EmployeeId = c.EmployeeId
            LEFT JOIN #Payroll d ON a.EmployeeId = d.EmployeeId

This my Screenshot table:

The result is exactly what I need. But, I am curious that if there another way to simplify the query. Any idea?
Thank you.

CodePudding user response：

You can use only 1 query instead of using temporary tables

--RESULT
SELECT      a.EmployeeId, a.EmployeeName, b.ReligionName, 
            ISNULL(c.EmployeeAddress, '-') EmployeeAddress, SUM(ISNULL(d.Amount,0)) Amount
FROM        Employee a
            INNER JOIN Religion b ON a.ReligionId = b.ReligionId
            LEFT JOIN Address c ON a.EmployeeId = c.EmployeeId and c.Seq = 1
            LEFT JOIN Payroll d ON a.EmployeeId = d.EmployeeId and d.PayrollType = 'THR'
GROUP BY a.EmployeeId, a.EmployeeName, b.ReligionName, c.EmployeeAddress