CODEWITHSUNDEEP
t-sql
PositiveGuy
PositiveGuy

Reputation: 47743

SQL Join Statement Issue

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

Upvotes: 2

Views: 117

Answers (4)

Mark Byers
Mark Byers

Reputation: 838106

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

Upvotes: 1

zapping
zapping

Reputation: 4116

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.

Upvotes: 0

HLGEM
HLGEM

Reputation: 96552

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.

Upvotes: 0

Blorgbeard
Blorgbeard

Reputation: 103447

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

Upvotes: 2

Related Questions