Re: Left Join, Subselect and Limit

[Ben Einstein]

After playing around a bit, I've figured out a very complex work around. It's a double subquery:

"SELECT SomeFields... FROM CD
LEFT JOIN
(SELECT SomeOtherFields... FROM Transaction JOIN
(SELECT MAX(Transaction.Date) AS MaxDate, Transaction_CD_fk FROM Transaction WHERE SomeConditions.... GROUP BY Transaction_CD_fk) AS TransactionMax
ON CD_pk = TransactionMax.Transaction_CD_fk AND Transaction.Date = TransactionMax. MaxDate) AS TransactionSubquery
ON CD.CD_pk = TransactionSubquery.Transaction_cd_fk"

It's insanely ugly, but it works.

Is there a better way to do this?