Insist on number of rows on left side of join

I have an issue where I want to join two tables where there is 1 record on the left and many on the right side but only need information from the right that exists in all of those records – in other words, insist on only getting 1:1 not 1:n in my results.

Example, if I run this statement below, I get 2 records for the WHERE statement:

SELECT TOP (1000) bi.*
FROM [DB4_Default].[dbo].[t_LinkBreakInfo] AS bi
WHERE bi.LinkID = 58150701 

When I include a join to a table which contains more info than returned from the query above, it finds 7 records on the right hand side. So, the results of the statement below results in 2 x 7 = 14 rows.

SELECT TOP (1000) bi.*, nav.security_name
FROM [DB4_Default].[dbo].[t_LinkBreakInfo] AS bi
  JOIN [DB4_Default].[dbo].[Positions] AS nav
    ON bi.LinkID = nav.link_identifier
WHERE bi.LinkID = 58150701 
  AND nav.data_origin_type = 'Local'

I still want just 2 records as per the first statement example but with the addition of the column value from the join.

Any ideas?

Answer

You want just any security_name for a bi row, no matter which. Let’s take the minimum (first in the alphabet). The most simple way is to select it in the SELECT clause:

SELECT
  bi.*, 
  (
    SELECT MIN(nav.security_name)
    FROM db4_default.dbo.positions AS nav
    WHERE nav.link_identifier = bi.linkid
    AND nav.data_origin_type = 'Local'
  ) AS secname
FROM db4_default.dbo.t_linkbreakinfo bi
WHERE bi.linkid = 58150701;

Alternatively in the FROMclause:

SELECT bi.*, nav.secname
FROM db4_default.dbo.t_linkbreakinfo bi
LEFT JOIN
(
  SELECT link_identifier, MIN(security_name) AS secname
  FROM db4_default.dbo.positions
  WHERE data_origin_type = 'Local'
  GROUP BY link_identifier
) nav ON nav.link_identifier = bi.linkid
WHERE bi.linkid = 58150701;