May 13, 2005 at 9:21 am
Can anyone provide a sample code for the following scenario:
Select some columns from table a and one column from table b. Join the two tables on a matching column, i.e. Id = ID. Return only record from this where b.value = min(b.value).
Thanks
May 13, 2005 at 9:46 am
select * from
TableA as A
INNER JOIN TableB as B ON B.ID = A.ID
WHERE B.value = (SELECT MIN(B.value) FROM B)
I think this will work for you.
May 13, 2005 at 10:56 am
Thanks Mark but that did not work. Here's the query:
SELECT LDR.LoanNumber
,LDR.StatusCode
,LDR.StatusCodeDate
,LDH.Item
FROM viewLoanDatatotalRetail AS LDR
INNER JOIN tblLoanDataLockHist AS LDH ON LDH.LoanNumber = LDR.LoanNumber
WHERE LDH.Item = (SELECT MIN(LDH.Item) FROM tblLoanDataLockHist LDH)
AND LDR.LoanNumber = '0000009333'
The min(item) value for this loannumber is 994 and the query, as written, returns no records because the statement (Select Min(LDH.Item) FROM tblLoanDataLockHist LDH) is selecting the min(item) value for the whole tblLoanDataLockHist table whish is 764.
Any other thoughts or suggestions?
May 13, 2005 at 11:25 am
In all fairness to Mark he got it right! He's solution works for the question you originally stated.
May 13, 2005 at 12:18 pm
Not trying to knock Mark at all and I apologize if it sounded that way. I appreciate any and all suggestions.
Thank you Mark
May 13, 2005 at 12:49 pm
Does this work?
SELECT LDR.LoanNumber
,LDR.StatusCode
,LDR.StatusCodeDate
,LDH.Item
FROM viewLoanDatatotalRetail AS LDR
INNER JOIN tblLoanDataLockHist AS LDH ON LDH.LoanNumber = LDR.LoanNumber
WHERE LDH.Item =
(SELECT MIN(LDH.Item)
FROM tblLoanDataLockHist LDH
Join viewLoanDatatotalRetail AS LDR
On LDH.LoanNumber = LDR.LoanNumber
Where LDR.LoanNumber = '0000009333')
AND LDR.LoanNumber = '0000009333'
May 13, 2005 at 1:32 pm
No worries Rick
This might work as well for you
SELECT MIN(LDH.Item) FROM (
SELECT LDR.LoanNumber
,LDR.StatusCode
,LDR.StatusCodeDate
,LDH.Item
FROM viewLoanDatatotalRetail AS LDR
INNER JOIN tblLoanDataLockHist AS LDH ON LDH.LoanNumber = LDR.LoanNumber
Where LDR.LoanNumber = '0000009333')
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply