Join query but select only first record from the second table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Join query but select only first record from the second table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-10 : 08:42:23

aru writes "Hi,
I have a customers table as follows
dbo.Customers.Customer_No, dbo.Customers.Customer_Name, dbo.Customers.[Date]

LoanGiven table as follows
dbo.LoanGiven.TransactionID, dbo.LoanGiven.Customer_No,
dbo.LoanGiven.DateLoanGiven, dbo.LoanGiven.AmountGiven

Now, I want to know the list of customers who borrowed the money (dbo.LoanGiven.DateLoanGiven) from 1/Jan/2004 to 31/Jan/2004 for the FIRST TIME. But, they might have taken the money subsequently but I'm looking for the records whereby the first Loan Given should be from 1/Jan/2004 to 31/Jan/2004.

i need the following fields to displayed :
dbo.Customers.Customer_No, dbo.Customers.Customer_Name,
dbo.Customers.[Date], dbo.LoanGiven.DateLoanGiven , dbo.LoanGiven.AmountGiven

The moment i add AmountGiven, MANY records with the same Customer_No appearing. I just want ONLY 1 record for 1 customer (if the record are in the range for the Loan given based on the date criteria when the Loan Given for the FIRST TIME) .

SELECT TOP 100 PERCENT a.*, b.FirstTimeDate AS FirstTimeDate, b.AmountGiven AS AmountGiven
FROM dbo.Customers a INNER JOIN
(SELECT Customer_No, MIN(DateLoanGiven) AS
FirstTimeDate, AmountGiven
FROM dbo.LoanGiven
GROUP BY Customer_No, AmountGiven) b ON a.Customer_No = b.Customer_No
WHERE (b.FirstTimeDate > '1/Jul/1994') AND (b.FirstTimeDate < '1/1/2005')
ORDER BY b.Customer_No"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-10 : 08:50:10

SELECT TOP 100 PERCENT a.*, b.FirstTimeDate AS FirstTimeDate, b.AmountGiven AS AmountGiven
FROM dbo.Customers a INNER JOIN
(SELECT Customer_No, DateLoanGiven AS
FirstTimeDate, AmountGiven, firstLoan = case when not exists(Select 1 From dbo.LoanGiven Where Customer_No = Z.Customer_No and dateLoanGiven < Z.dateLoanGiven) then 1 else 0 end
FROM dbo.LoanGiven Z) b ON a.Customer_No = b.Customer_No
WHERE (b.FirstTimeDate > '1/Jul/1994') AND (b.FirstTimeDate < '1/1/2005')
and FirstLoan = 1
ORDER BY b.Customer_No"

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources