title: SQL join table on top value only
body: <p>I have two tables (Temp1 &amp; Temp2 in this example) that join together.  Some IDs in Temp1 have multiple ParentIDs that link to Temp2, but I only want 1 ParentID to actually return.  It doesn't matter which one, so let's just say the top value ID in Temp2.</p>
  
  <p>The following code provides a good example of where I'm starting</p>
  
  <pre><code>IF OBJECT_ID('tempdb..#Temp1') IS NOT NULL
      DROP TABLE #Temp1
  
  IF OBJECT_ID('tempdb..#Temp2') IS NOT NULL
      DROP TABLE #Temp2
  
  CREATE TABLE #Temp1
  (
      ID INT
      ,ParentID INT
      ,Name VARCHAR(10)
  )
  
  CREATE TABLE #Temp2
  (
      ID INT
      ,Name VARCHAR(15)
  )
  
  INSERT #Temp1 VALUES
  (1, 1, 'Apples'),
  (2, 1, 'Bananas'),
  (3, 2, 'Milk'),
  (3, 3, 'Milk'),
  (4, 3, 'Wine'),
  (4, 4, 'Wine')
  
  INSERT #Temp2 VALUES
  (1, 'Fruit'),
  (2, 'Dairy'),
  (3, 'Beverages'),
  (4, 'Beer &amp; Wine')
  
  SELECT
  T1.ID [ChildID]
  ,T1.Name [ChildName]
  ,T2.ID [ParentID]
  ,T2.Name [ParentName]
  FROM #Temp1 T1
  INNER JOIN #Temp2 T2 ON T2.ID = T1.ParentID
  </code></pre>
  
  <p>The desired result I'm looking for would be:</p>
  
  <pre><code>ChildID     ChildName   ParentID    ParentName
  1           Apples      1           Fruit
  2           Bananas     1           Fruit
  3           Milk        3           Beverages
  4           Wine        4           Beer &amp; Wine
  </code></pre>
  
  <p>I've seen other examples like this where people use CROSS APPLY, but I just can't seem to get it to work.  Any assistance is appreciated!</p>