.....IN (result Of Subquery)

Hi

I wonder if someone could help me with the following problem. As a minimalistic example I have three tables in my DB, tblShifts, tblPersonnel and tblCrew, created as

CREATE TABLE [dbo].[tblShifts](
[ShiftID] [varchar](4) NOT NULL,
[ShiftHours] [int] NULL)

CREATE TABLE [dbo].[tblPersonnel](
[PersonnelID] [varchar](15) NOT NULL,
[ShiftID] [varchar](4) NULL)

CREATE TABLE [dbo].[tblCrew](
[CrewID] [varchar](15) NOT NULL,
[ShiftID] [varchar](4) NULL)

My problem comes about as I want to sum the shift hours. I.e. my first attempt that failed was written as


SELECT SUM(dbo.tblShifts.ShiftHours)

FROM dbo.tblShifts

WHERE dbo.tblShifts.ShiftID IN

(

SELECT dbo.tblPersonnel.ShiftID
FROM dbo.tblPersonnel INNER JOIN
dbo.tblCrew ON dbo.tblPersonnel.ShiftID = dbo.tblCrew.ShiftID
)


The problem is that if the subquery returns a set of shift IDs with values repeated, such that the IN statement e.g. evaluates to 'IN (4,3,4)', the calculation is not right. In my calculation I want to add to the sum of shift hours for each instance of shift with ID = 4.

Does anyone have a good suggestion to solve this problem?

Kind regards
Jens Ivar