SQL Join Statement Issue

Question

I'm tring to grab all fields from the latest Cash record, and then all fields from the related TransactionInfo record. I can't quite get this to work yet:

select t.*, top 1 c.* from Cash c
inner join TransactionInfo t
on c.TransactionID = t.id
order by c.createdOn desc

Answer 1

What's that top 1 doing there? If you only want one row then the TOP(1) must come first:

SELECT TOP(1) t.*, c.*
FROM Cash c
INNER JOIN TransactionInfo t
ON c.TransactionID = t.id
ORDER BY c.createdOn DESC

Answer 2

select top 1 *
from Cash c
inner join TransactionInfo t on c.TransactionID = t.id
order by createdOn desc

Answer 3

select t.,c. from (Select top 1 * from Cash order by createdOn desc ) c inner join TransactionInfo t on c.TransactionID = t.id order by createdOn desc

DOn;t use select * especially with a join, it wastes server resources.

Answer 4

SELECT c.*, t.* FROM cash c, transactioninfo t
WHERE c.infoid = t.id AND c.createdOn = (SELECT max(createdOn) FROM cash WHERE infoId = t.id) ORDER BY transactiontabledate desc

You need to find the record with the latest date from the cash table for each transactionId and use that also to filter it out in your query.