There is sometimes a need to
JOIN
to a table, but only include the first result that matches the
JOIN
condition so the result records aren't duplicated by the
JOIN
. The typical way to do that is to use a subquery. However, if the fields on the table are used in more than one place, that can become cumbersome to add the same subquery several times, as in the following example:
DECLARE @FirstTable table(FirstName varchar(20))
DECLARE @SecondTable table
(ID int,
FirstName varchar(20),
LastName varchar(20),
FirstNameHashCode varchar(20),
FirstNameFrequency int)
INSERT INTO @FirstTable(FirstName) VALUES('Billy')
INSERT INTO @FirstTable(FirstName) VALUES('Super')
INSERT INTO @SecondTable(ID, FirstName, LastName, FirstNameHashCode, FirstNameFrequency)
VALUES(1, 'Billy', 'Mays', '0XB$22', 22)
INSERT INTO @SecondTable(ID, FirstName, LastName, FirstNameHashCode, FirstNameFrequency)
VALUES(2, 'Billy', 'TheKid', '0XB$22', 22)
INSERT INTO @SecondTable(ID, FirstName, LastName, FirstNameHashCode, FirstNameFrequency)
VALUES(3, 'Super', 'Man', 'SJ3JD', 40)
INSERT INTO @SecondTable(ID, FirstName, LastName, FirstNameHashCode, FirstNameFrequency)
VALUES(4, 'Super', 'Friends', 'SJ3JD', 40)
SELECT
(
SELECT TOP 1
ST.FirstNameHashCode
FROM @SecondTable AS ST
WHERE
ST.FirstName = FT.FirstName
),
(
SELECT TOP 1
ST.FirstNameFrequency
FROM @SecondTable AS ST
WHERE
ST.FirstName = FT.FirstName
),
(
SELECT TOP 1
ST.ID
FROM @SecondTable AS ST
WHERE
ST.FirstName = FT.FirstName
)
FROM @FirstTable AS FT
A better (at least in non-contrived cases) way to do this is to use what I call a "
TOP 1 JOIN
":
DECLARE @FirstTable table(FirstName varchar(20))
DECLARE @SecondTable table
(ID int,
FirstName varchar(20),
LastName varchar(20),
FirstNameHashCode varchar(20),
FirstNameFrequency int)
INSERT INTO @FirstTable(FirstName) VALUES('Billy')
INSERT INTO @FirstTable(FirstName) VALUES('Super')
INSERT INTO @SecondTable(ID, FirstName, LastName, FirstNameHashCode, FirstNameFrequency)
VALUES(1, 'Billy', 'Mays', '0XB$22', 22)
INSERT INTO @SecondTable(ID, FirstName, LastName, FirstNameHashCode, FirstNameFrequency)
VALUES(2, 'Billy', 'TheKid', '0XB$22', 22)
INSERT INTO @SecondTable(ID, FirstName, LastName, FirstNameHashCode, FirstNameFrequency)
VALUES(3, 'Super', 'Man', 'SJ3JD', 40)
INSERT INTO @SecondTable(ID, FirstName, LastName, FirstNameHashCode, FirstNameFrequency)
VALUES(4, 'Super', 'Friends', 'SJ3JD', 40)
SELECT
ST.FirstNameHashCode,
ST.FirstNameFrequency,
ST.ID
FROM @FirstTable AS FT
JOIN @SecondTable AS ST
ON ST.FirstName = FT.FirstName
AND EXISTS
(
SELECT
0
FROM @SecondTable AS STMany
JOIN
(
SELECT TOP 1
*
FROM @SecondTable AS STInner
WHERE
STInner.FirstName = FT.FirstName
) AS STTop1
ON STMany.ID = STTop1.ID
WHERE
STMany.ID = ST.ID
)
There is a simpler version, but this version works with tables that have primary keys composed of more than one column, so it is more generally applicable. The above example code is entirely self-contained, so go ahead and give it a run to see what it returns.