SQL Query question

[abarnett]

Have two tables UseCases and UseCaseNotes, where UseCases is the parent table. Trying to get the name, owner, start date, and status along with the latest Note Date, and the associated resource and description.

I started with the query below, but am having some dificulty in completing it. Anyone who can help?

SELECT UseCases.Name, UseCases.Description, UseCases.Owner, UseCases.Status, UseCases.StartDate, MAX(UseCaseNotes.NoteDate), UseCaseNotes.Resource, UseCaseNotes.Description
FROM UseCases, UseCaseNotes
WHERE UseCases.UseCaseID=UseCaseNotes.UseCaseID
ORDER BY UseCases.Owner;

Thanks for the assist!

A

 

[Jon K]

You can do it with a series of three queries.

qryOne:-
SELECT UseCaseID, Max(NoteDate) AS MaxOfNoteDate
FROM UseCaseNotes
GROUP BY UseCaseID;

qryTwo:-
SELECT UseCaseNotes.*
FROM UseCaseNotes INNER JOIN qryOne ON (UseCaseNotes.UseCaseID = qryOne.UseCaseID) AND (UseCaseNotes.NoteDate = qryOne.MaxOfNoteDate);

qryThree:-
SELECT UseCases.Name, UseCases.Description, UseCases.Owner, UseCases.Status, UseCases.StartDate, qryTwo.NoteDate, qryTwo.Resource, qryTwo.Description
FROM UseCases INNER JOIN qryTwo ON UseCases.UseCaseID = qryTwo.UseCaseID;

Run the third query.


Alternatively, you can do it in one query based on table UseCases and in the query use correlated subqueries or domain functions to get the associated values from the other table, but it will be slow if the tables are large.

Inner joins are more efficient than correlated subqueries and correlated domain functions.
.