Getting the most recent records between 2 tables

Question

Hello,

This is a continuation of this question. I worked all weekend on this problem without solving it. I'm totally stumped. Essentially, I inherited an interesting table structure. This table structure looks like the following:

GroupTable

* ID
* Name
* Description

GroupItem

* ID
* GroupID
* Name
* CreatedBy
* CreationDate

OpenGroupItem

* 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 need associated with the GroupID. The three GroupItem elements I retrieve must have at least one other GroupItem element with the same GroupID. The second twist is that the other items in the group may be in OpenGroupItem.

In other words, I am trying to retrieve the three most recent GroupTables that have at least two items in the group. The items can be in either GroupItem or OpenGroupItem. The catch is that at least one of the items must 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

My first recommendation would be to fix your database structure. There is no need for these to be in two tables with the same structure. One table with a field to indicate if it is open is a better design. To keep things from breaking, you name the table something new and then create a view for GroupItem (that is named the same as that table) and one for OpenGroupITem that pick out just the records you want for each based onteh field you added. You would also need to change the process that moves records from open to not open status if you have such a thing.

Once that is done it is trivial to query the data.

If you can't do that, the best solution is to UNION ALL the two tables together and then query. If you will have to do this type of query frequently, make the UNOIN ALL a view. If this is a rare occurance, make it a derived table or CTE or temp table.