UsefulSQL/OuterApply.sql at master · rakemen/UsefulSQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
IF OBJECT_ID('tempdb..#Fund') IS NOT NULL DROP TABLE #Fund
CREATE TABLE #Fund (FundID INT, FundName NVARCHAR(255))

INSERT INTO #Fund VALUES (800512, 'Fund 1')
INSERT INTO #Fund VALUES (800513, 'Fund 2')
INSERT INTO #Fund VALUES (800514, 'Fund 3')
INSERT INTO #Fund VALUES (800515, 'Fund 4')
INSERT INTO #Fund VALUES (800516, 'Fund 5')
INSERT INTO #Fund VALUES (800517, 'Fund 6')

IF OBJECT_ID('tempdb..#Documents') IS NOT NULL DROP TABLE #Documents
CREATE TABLE #Documents ( FK_FundID INT, DocumentID INT )

INSERT INTO #Documents VALUES (800512, 123)
INSERT INTO #Documents VALUES (800512, 234)
INSERT INTO #Documents VALUES (800514, 512)
INSERT INTO #Documents VALUES (800515, 245)
INSERT INTO #Documents VALUES (800515, 765)
INSERT INTO #Documents VALUES (800516, 987)
INSERT INTO #Documents VALUES (800512, 345)
INSERT INTO #Documents VALUES (800514, 008)
INSERT INTO #Documents VALUES (800517, 556)
INSERT INTO #Documents VALUES (800518, 990)

SELECT f.FundName, d.Ct
FROM #Fund f
OUTER APPLY (SELECT COUNT(DocumentID) AS Ct FROM #Documents WHERE FK_FundID = f.FundID) d