Editable view without duplicates

I'm having a mental block about how to make a view displaying the
records I need that's editable and not read-only. Given the following
basic table structure:

CREATE TABLE [dbo].[Person](
[PersonID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](255) NULL,
[Surname] [nvarchar](255) NULL)

CREATE TABLE [dbo].[Contract](
[ContractID] [int] IDENTITY(1,1) NOT NULL,
[PersonID] [int] NULL,
[PostID] [int] NULL)

CREATE TABLE [dbo].[Post](
[PostID] [int] IDENTITY(1,1) NOT NULL,
[PostTitle] [nvarchar](50) NULL,
[Department] [nvarchar](2) NULL)

I want to select the records from [Person] who have a contract
corresponding to a [Post] in a particular department. If I do the
basic

SELECT PersonID, FirstName, Surname
FROM Person INNER JOIN
Contract ON Person.PersonID = Contract.PersonID
INNER JOIN
Post ON Contract.PostID = Post.PostID
WHERE (Post.Department = N'Department')

I obviously get duplicates where there are multiple contracts for one
person. What I need is something that does the same job as

SELECT PersonID, FirstName, Surname
FROM Person INNER JOIN
(SELECT DISTINCT Contract.PersonID
FROM Contract INNER JOIN
Post ON
Contract.PostID = Post.PostID
WHERE (Post.Department =
'Department')) AS DepartmentFilter ON Person.PersonID =
DepartmentFilter.PersonID

but which lets me edit the information returned - this version is read-
only.

Bernard West ( ) writes:
> I want to select the records from [Person] who have a contract
> corresponding to a [Post] in a particular department. If I do the
> basic
>
> SELECT PersonID, FirstName, Surname
> FROM Person INNER JOIN
> Contract ON Person.PersonID = Contract.PersonID
> INNER JOIN
> Post ON Contract.PostID = Post.PostID
> WHERE (Post.Department = N'Department')
>
> I obviously get duplicates where there are multiple contracts for one
> person. What I need is something that does the same job as
>
> SELECT PersonID, FirstName, Surname
> FROM Person INNER JOIN
> (SELECT DISTINCT Contract.PersonID
> FROM Contract INNER JOIN
> Post ON
> Contract.PostID = Post.PostID
> WHERE (Post.Department =
> 'Department')) AS DepartmentFilter ON Person.PersonID =
> DepartmentFilter.PersonID
>
> but which lets me edit the information returned - this version is read-
> only.

Rather than joining, use EXISTS:

SELECT P.PersonID, P.FirstName, P.Surname
FROM Person P
WHERE EXISTS (SELECT *
FROM Contracts C
JOIN Post ON C.PostID = Post.PostID
WHERE Post.Department = 'Department'
AND P.PersonID = C.PersonID)

--
Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx