SQL with a JOIN to the TOP 1 of another table

So I needed to do a join where a table would join only to the top row returned from the second table. In fact in this one I needed to have 2 joins that do that, so here is how I created it:

SELECT d.TableName,d.GroupName,d.CircuitID,d.ServiceIndicator,d.ErrorPriorityCode,d.OmniaAddressID,d.LegacyKeyID,d.ServiceIndicatorSource,
assignment.AssignedBy_UserId as LastAssignedBy,
assignment.AssignedTo_UserId as LastAssignedTo,
assignment.Comment as LastAssignmentComment,
assignment.DateAssigned as LastDateAssigned,
checkout.UserId as LastCheckedOutBy,
checkout.ResolutionId as LastCheckoutResolutionId,
checkout.Comments as LastCheckoutComment,
checkout.Closed as LastCheckoutClosed,
checkout.CheckoutDate as LastCheckoutDate,
checkout.CheckinDate as LastCheckInDate

FROM dbo.Details AS d
LEFT JOIN circuitjoin AS cmCircuit
ON d.CircuitID = cmCircuit.Id
Left Join dbo.DetailAssignment assignment
ON assignment.RowId =
(select top 1 a.RowId from DetailAssignment a
where a.DetailServiceIndicator = d.ServiceIndicator
order by a.DateAssigned desc)

LEFT JOIN dbo.DetailCheckout AS checkout
ON checkout.RowId =
(Select top 1 c.RowId from dbo.DetailCheckout c
where c.DetailServiceIndicator = d.ServiceIndicator
order by c.CheckoutDate desc)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s