SQL Server – prevent the parent table repeating rows with child table data

Often I have to bring in data to a parent table from a child table ( i.e. has a case been invoiced?). This example SQL sets up two tables . A parent table (tblTestCase) and a child table (tblTestInvoice)

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

--this would duplicate rows
SELECT * FROM tblTestCase a LEFT OUTER JOIN tblTestInvoice b ON a.CaseId = b.CaseId

as the comment above the SELECT says , a join would duplicate rows on the resultset as some cases have more than one invoice.

A subselect is the quickest/easiset solution:

--subselect per row
SELECT *, (SELECT TOP 1 FeeId FROM tblTestInvoice WHERE CaseId = CaseId) FROM tblTestCase

But subselects are always a bad idea.

This is what I use:

SELECT * FROM tblTestCase
LEFT OUTER JOIN (SELECT DISTINCT CaseId As InvoiceCaseID FROM tblTestInvoice)
As tblTestCaseInvoice ON tblTestCaseInvoice.InvoiceCaseID = tblTestCase.CaseId

The join is made on a “pseudo-table” of distinct CaseIds which ensures no repeating of rows from the parent table.

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