Using a filter on a column on related (joined) tables

Category: How do I?
Date: 19 Nov 2008
Product: SQL Data Compare
It can be useful to filter records by a column's relationship with a related table - but the "'WHERE clause" option in SQL Data Compare "Project Configuration" does not offer direct access to columns in two "related" tables. In this article we examine how a view can be used instead.

Consider two tables, Sales and LineItems. Lets say a record in LineItems belongs to a district in the Sales table. I want to only compare Lineitems records that belong in a certain district "Suffolk", and ignore all the other districts.

To run the example, create two databases, TEST and TEST2. Both have these databases have tables LineItems, Sales and a view View_1:
CREATE TABLE [dbo].[LineItems](
[ID] [smallint] NOT NULL,
[description] [nvarchar](50) NULL,
[SalesID] [smallint] NULL,
CONSTRAINT [PK_LineItems] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Sales](
[SalesID] [smallint] NOT NULL,
[SalesDescription] [nvarchar](50) NULL,
CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED
(
[SalesID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Create VIEW VIEW_1 AS
SELECT dbo.LineItems.ID, dbo.LineItems.description, dbo.LineItems.SalesID, dbo.Sales.SalesDescription
FROM dbo.LineItems INNER JOIN
dbo.Sales ON dbo.LineItems.SalesID = dbo.Sales.SalesID
WHERE (dbo.Sales.SalesDescription = 'Suffolk')

--Populate TEST with the following:
INSERT INTO [dbo].[LineItems] ([ID], [description], [salesID]) VALUES (1, N'Fred ', 1)
INSERT INTO [dbo].[LineItems] ([ID], [description], [salesID]) VALUES (2, N'Chrs ', 1)
INSERT INTO [dbo].[LineItems] ([ID], [description], [salesID]) VALUES (3, N'Steve ', 1)
INSERT INTO [dbo].[LineItems] ([ID], [description], [salesID]) VALUES (4, N'Greg ', 2)
INSERT INTO [dbo].[LineItems] ([ID], [description], [salesID]) VALUES (5, N'Clare ', 2)
INSERT INTO [dbo].[LineItems] ([ID], [description], [salesID]) VALUES (6, N'David ', 2)
INSERT INTO [dbo].[LineItems] ([ID], [description], [salesID]) VALUES (7, N'Linda ', 3)

--And ...
INSERT INTO [dbo].[Sales] ([SalesID], [SalesDescription]) VALUES (1, N'Norfolk')
INSERT INTO [dbo].[Sales] ([SalesID], [SalesDescription]) VALUES (2, N'Suffolk')
INSERT INTO [dbo].[Sales] ([SalesID], [SalesDescription]) VALUES (3, N'Hampshire')
INSERT INTO [dbo].[Sales] ([SalesID], [SalesDescription]) VALUES (4, N'Essex')

Start SQL Data Compare - set the Project Configuration Options / Include Views to ticked on.

Sychronize TEST and TEST2. Now when clicking on VIEW_1 row the inserts created by the update script are:

INSERT INTO [dbo].[View_1] ([ID], [salesID], [SalesDescription], [description]) VALUES (4, 2, N'Suffolk', N'Greg ')
INSERT INTO [dbo].[View_1] ([ID], [salesID], [SalesDescription], [description]) VALUES (5, 2, N'Suffolk', N'Clare ')
INSERT INTO [dbo].[View_1] ([ID], [salesID], [SalesDescription], [description]) VALUES (6, 2, N'Suffolk', N'David ')

We are therefore limiting the update SQL script to only the "Suffolk". In this way we can see how a simple view can control the comparison output by linking related tables.