SQL Query Subtracting Values from Two Tables

I have two tables, demand and fte, that have department, shift and demand or fte. I need to calculate the need (demand – fte). What I get is a table with 9 entries. I’ve run into this before and for the life of me I cannot remember how I resolved it.

There is also a department table which I neglected. It only holds the departmentID & Name

select 
    dp.[Department], dm.[shift], (dm.Demand - ft.fte) as Need
from
    tblDemand dm
right join 
    [tblDepartment] dp on dp.[DepartmentID] = dm.[DepartmentID]
right join 
    [tblActual_FTE] ft on ft.[DepartmentID] = dm.[DepartmentID]
where
    dp.Department = 'screw ii'

Demand

department	shift	demand
A1	1	23
A1	2	26
A1	3	21

FTE

department	shift	fte
A1	1	26
A1	2	24
A1	3	18

Expected result:

department	shift	need
A1	1	-3
A1	2	2
A1	3	3

Current results:

department	shift	need
A1	1	-3
A1	2	0
A1	3	-5
A1	1	-1
A1	2	2
A1	3	-3
A1	1	5
A1	2	8
A1	3	3

Answer

your join should be based on departmentid and shift :

select 
    dp.[Department]
    , dm.[shift]
    , (dm.Demand - ft.fte) as Need
from tblDemand dm
right join [tblDepartment] dp on dp.[DepartmentID] = dm.[DepartmentID]
right join [tblActual_FTE] ft 
  on ft.[DepartmentID] = dm.[DepartmentID]
  and ft.[shift] = dm.[shift]
where dp.Department = 'screw ii'