Limiting the Number of Records Joined

Question

How can I perform a query that contains a (one-to-many) join between two tables, which will only join up to a pre-defined number of joined records?

So, lets say I have two tables, one for Matches, and one for Goals, and I want to perform some analysis on the matches based on the first 3 goals that have been scored in it. The idea would be to execute a single query, and I would then have a list of comments that is only comprised of the most recent 5 comments on each post

Matches:

+----------+
| Match ID |
+----------+
| 1        |
+----------+
| 2        |
+----------+
| 3        |
+----------+

Goals:

+---------+----+
| Goal ID | In | 
+---------+----+
| 1       | 2  |
+---------+----+
| 2       | 2  |
+---------+----+
| 3       | 1  |
+---------+----+
| 4       | 3  |
+---------+----+
| 5       | 1  |
+---------+----+
| 6       | 2  |
+---------+----+
| 7       | 3  |
+---------+----+
| 8       | 2  |
+---------+----+
| 9       | 1  |
+---------+----+

Result: (limit to first 2)

+---------+----+
| Goal ID | In | 
+---------+----+
| 1       | 2  |
+---------+----+
| 2       | 2  |
+---------+----+
| 3       | 1  |
+---------+----+
| 4       | 3  |
+---------+----+
| 5       | 1  |
+---------+----+
| 7       | 3  |
+---------+----+

Answer 1

In SQL Server and other systems that support ROW_NUMBER()

you can use this syntax:

WITH GoalsWithGoalNum AS (
    SELECT *, 
        ROW_NUMBER() OVER (
            PARTITION BY [In] 
            ORDER BY [Goal ID]
        ) AS GoalNum
    FROM Goals
)
SELECT *
FROM GoalsWithGoalNum
WHERE GoalNum <= 2;

Answer 2

You can use a table variable and a windowing function to do this.

Declare @Goals
([Match id] int
,[Goal Id] int
,[GoalCount] int)

Insert into @Goals
SELECT [In], [Goal Id], COUNT([Goal Id]) OVER (PARTITION BY [In] ORDER BY [Goal Id]) AS [GoalCount]
FROM Goals 

Select * 
From Matches m 
JOIN @Goals g on m.[Match Id] = g.[Match Id] and g.GoalCount <= 3