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.
Alex Graham 