Getting the most recent records with 2 or more related entries with SQL

Question

Hello,

I inherited an interesting table structure. This table structure looks like the following:

GroupTable

• ID
• Name
• Description

GroupItem

• ID
• GroupID
• Name
• CreatedBy
• CreationDate

I need to get the three most recent GroupItem elements created by a specific user. The twist comes in with a requirement associated with the GroupID. The three GroupItem elements I retrieve must have at least one other GroupItem element with the same GroupID.

In other words, I am trying to retrieve the three most recent GroupTables that have at least two GroupItem elements where at least one of those GroupItem elements have been created by a specific user.

I have no clue how to accomplish this in SQL. I am using SQL Server 2008 and I keep coming up short.

Answer 1

SELECT TOP 3
    GI1.ID,
    GI1.GroupID,
    GI1.Name,
    GI1.CreatedBy,
    GI1.CreationDate
FROM
    GroupTable GT
INNER JOIN GroupItems GI1 ON
    GI1.GroupID = GT.ID AND
    GI1.CreatedBy = @user
WHERE
    EXISTS
    (
        SELECT *
        FROM
            GroupItems GI2
        WHERE
            GI2.GroupID = GT.ID AND
            GI2.ID <> GI1.ID
    )
ORDER BY
    GT1.CreationDate DESC

Answer 2

As Tom H. was way faster in constructing the solution for your first definition I will do the second one :)

SELECT TOP 3
    GT.ID
FROM
    GroupTable GT
    INNER JOIN GroupItem GI1 ON GI1.GroupID = GT.ID
WHERE
    GT.ID IN 
    (
    SELECT GroupID
    FROM
        GroupItem GI2
    WHERE
        GI2.ID = GT.ID AND
        GI2.CreatedBy = @user
    )
GROUP BY 
    GT.ID
HAVING 
    Count(*) >= 2
ORDER BY
    MAX(GI1.CreationDate) DESC

Main difference is that this query groups by GroupTable so there can be no case when the same table is returned more than once (which happens in Tom H. answer/is required in the first definition of the problem).