SQL Server – methods to prevent repeating rows on a parent table

Every so often I have to bring in some information to a parent table from child tables. The foreign key in the child tables would cause the parent table to repeat rows in the resultset. The following sql sets up two tables as an example. tblTestCase being the parent table:

CREATE TABLE [dbo].[tblTestCase](
[CaseId] [int] IDENTITY(1,1) NOT NULL,
[Description] varchar(50) NULL,
) ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblTestInvoice](
[InvoiceId] [int] IDENTITY(1,1) NOT NULL,
[CaseId] [int] NULL,
[Fee] [money] NULL
) ON [PRIMARY]

GO

INSERT INTO [tblTestCase]
([Description])
VALUES
('Case1')
GO

INSERT INTO [tblTestCase]
([Description])
VALUES
('Case2')
GO

INSERT INTO [tblTestCase]
([Description])
VALUES
('Case3')
GO

INSERT INTO [tblTestCase]
([Description])
VALUES
('Case4')
GO

INSERT INTO [tblTestCase]
([Description])
VALUES
('Case5')
GO

INSERT INTO [tblTestInvoice]
([CaseId]
,[Fee])
VALUES
(1
,10.00)
GO

INSERT INTO [tblTestInvoice]
([CaseId]
,[Fee])
VALUES
(1
,20.00)
GO

INSERT INTO [tblTestInvoice]
([CaseId]
,[Fee])
VALUES
(1
,30.00)
GO
INSERT INTO [tblTestInvoice]
([CaseId]
,[Fee])
VALUES
(2
,40.00)
GO

INSERT INTO [tblTestInvoice]
([CaseId]
,[Fee])
VALUES
(2
,60.00)
GO

INSERT INTO [tblTestInvoice]
([CaseId]
,[Fee])
VALUES
(3
,100.00)
GO

SELECT * FROM tblTestCase
SELECT * FROM tblTestInvoice

The easiest way to prevent repeating in the resultset is by using subselects:

SELECT *, (SELECT TOP 1 FeeId FROM tblTestInvoice WHERE CaseId = CaseId) FROM tblTestCase

However subselects are not efficient. A better way that I would use is a pseudo table created from an sql query which you can join to
–cleaner method to left join on pseudo table
SELECT * FROM tblTestCase
LEFT OUTER JOIN (SELECT DISTINCT CaseId As InvoiceCaseID FROM tblTestInvoice)
As tblTestCaseInvoice ON tblTestCaseInvoice.InvoiceCaseID = tblTestCase.CaseId

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s