A more efficient nested SELECT? (AC2007)

[AOB]

Hi guys,

Looking for a more efficient way of nesting a subquery.

The below 'works' :

SELECT [tblIssues].[IssueID], [tblIssues].[IssueResolveDate], [tblIssues].[Amount], ([tblIssues].[Amount]*[tblRates].[FXRate]) AS AmountUSD, [tblIssueTypes].[IssueType],

(SELECT TOP 1 [tblComments].[Comment] FROM [tblComments] WHERE [tblComments].[IssueID]=[tblIssues].[IssueID] ORDER BY [tblComments].[UpdatedWhen] DESC) AS Comment,

(SELECT TOP 1 [tblComments].[UpdatedBy] FROM [tblComments] WHERE [tblComments].[IssueID]=[tblIssues].[IssueID] ORDER BY [tblComments].[UpdatedWhen] DESC) AS UpdatedBy,

(SELECT TOP 1 [tblComments].[UpdatedWhen] FROM [tblComments] WHERE [tblComments].[IssueID]=[tblIssues].[IssueID] ORDER BY [tblComments].[UpdatedWhen] DESC) AS UpdatedWhen

FROM ((([tblIssues]

INNER JOIN [tblBalances] ON [tblIssues].[AccountID] = [tblBalances].[AccountID])

INNER JOIN [tblRates] ON ([tblBalances].[BalanceDate] = [tblRates].[RateDate] AND [tblBalance
s].[CcyID] = [tblRates].[CcyID]))

INNER JOIN [tblIssueTypes] ON [tblIssues].[IssueTypeID] = [tblIssueTypes].[IssueTypeID])

WHERE [tblBalances].[BalanceDate] = #01/02/2014#

AND [tblIssues].[AccountID] = 123

AND ([tblIssues].[ValueDate] <= #01/02/2014#

AND ([tblIssues].[IssueResolveDate] > #01/02/2014# OR [tblIssues].[IssueResolveDate] Is Null))

But I'm not happy with the piece in red (subquery to select the latest comment for each issue; one-to-many relationship between the issues table and the comments table)

I need to get three fields back from the comments table but it's the same query each time. Rather than have it in there 3 times, it would make more sense to run it once and refer to the three returned fields of the subquery?

Something along the lines of :

SELECT [tblIssues].[IssueID], [tblIssues].[IssueResolveDate], [tblIssues].[Amount], ([tblIssues].[Amount]*[tblRates].[FXRate]) AS AmountUSD, [tblIssueTypes].[IssueType], SubQry.Comment, SubQry.UpdatedBy, SubQry.UpdatedWhen

(SELECT TOP 1 [tblComments].[Comment], [tblComments].[UpdatedBy], [tblComments].[UpdatedWhen] FROM [tblComments] WHERE [tblComments].[IssueID]=[tblIssues].[IssueID] ORDER BY [tblComments].[UpdatedWhen] DESC) AS SubQry

FROM ((([tblIssues]

INNER JOIN [tblBalances] ON [tblIssues].[AccountID] = [tblBalances].[AccountID])

INNER JOIN [tblRates] ON ([tblBalances].[BalanceDate] = [tblRates].[RateDate] AND [tblBalance
s].[CcyID] = [tblRates].[CcyID]))

INNER JOIN [tblIssueTypes] ON [tblIssues].[IssueTypeID] = [tblIssueTypes].[IssueTypeID])

WHERE [tblBalances].[BalanceDate] = #01/02/2014#

AND [tblIssues].[AccountID] = 123

AND ([tblIssues].[ValueDate] <= #01/02/2014#

AND ([tblIssues].[IssueResolveDate] > #01/02/2014# OR [tblIssues].[IssueResolveDate] Is Null))

But I don't know how to get the syntax right to do it this way. As in, where to position the nested SELECT statement in such a way that I can refer to it in the main query.

Appreciate I can leave it just the way it is and it will 'work' but the OCD in me has me thinking there is a more efficient way of doing this and I'd prefer to learn the 'correct' way rather than just leaving it be.

Can anybody help?

Thanks

AOB

 

[plog]

Do you have a primary key on tblIssues? If so, you run a query to get the ID with the maximum [UpdatedWhen] value. You can then use that ID to link back into tblIssues and pull the entire record at once instead of 3 times.

 

[AOB]

Hi plog,

The [IssueID] is the primary key for [tblIssues]

But not sure how put together the SQL to do what you're suggesting?

(SQL novice here )

Thanks

AOB