Query to return TOP X results in a 1-M relationship

[Seguer]

Hi guys,

I have a 1-M relationship where 1 Task will have Many updates. In a query I need to get all Tasks within a criteria, but only the Top 2 updates. I also need to be able to show Tasks even if they have no updates. I think this is possible using the TOP predicate and subqueries, but for the life of me I can't figure it out.

Tables:

Data_Tasks
Data_Task_Update_History


Thanks

 

[mp9]

I've asssumed your table structure to be something like:

Data_Tasks
TaskID
....

Data_Task_Update_History
ID
TaskID
UpdateDate
...

SELECT Data_Tasks.*, Data_Task_Update_History.*
FROM Data_Tasks LEFT JOIN Data_Task_Update_History ON Data_Tasks.TaskID = Data_Task_Update_History.TaskID
WHERE Data_Task_Update_History.ID In (SELECT TOP 2 ID FROM Data_Task_Update_History WHERE TaskID=Data_Tasks.TaskID ORDER BY UpdateDate DESC);

http://www22.brinkster.com/accessory/

 

[Seguer]

Thanks for the reply mp9. The table structure is as follows:

Data_Tasks
ID
....

Data_Task_Update_History
history_ID
task_ID
date_of_update
...

---------------------------------------

When I changed your code to reflect my table structure I get no results. When I changed it again to this:

SELECT Data_Tasks.*, Data_Task_Update_History.*, Data_Task_Update_History.history_ID
FROM Data_Tasks LEFT JOIN Data_Task_Update_History ON Data_Tasks.ID = Data_Task_Update_History.task_ID
WHERE (((Data_Task_Update_History.history_ID) In (SELECT TOP 2 ID FROM Data_Task_Update_History WHERE task_ID=Data_Tasks.ID ORDER BY date_of_update DESC)));

I get results, but not the TOP 2 updates.

 

[Seguer]

Ah, nevermind. I found the problem. I forgot to rename the SELECT TOP 2 ID to SELECT TOP 2 history_ID. It all works now. Thanks mp9.

SELECT Data_Tasks.*, Data_Task_Update_History.*
FROM Data_Tasks LEFT JOIN Data_Task_Update_History ON Data_Tasks.ID = Data_Task_Update_History.task_ID
WHERE Data_Task_Update_History.history_ID In (SELECT TOP 2 history_ID FROM Data_Task_Update_History WHERE task_ID=Data_Tasks.ID ORDER BY date_of_update DESC);

 

[Seguer]

Sorry to bother again, however this doesn't seem to pull out tasks that have no update. I think the logic behind that is because it is looking for ID's "in" a query result, when a task has no updates it's ID won't be there to be found. Is there any way around this?

 

[Seguer]

Nevermind, had a brainstorm. To get tasks with no updates I added an "Or Is Null" after the subquery.