Showing values from subquery

[dosview]

I have a table Customers with 2 fields (Id, FullName) and a table Payments with two fields (CustId, Amount). In Payments a given customer may occurr in more records if he made more than 1 payment.

The following query extracts the names of the customers who made at least 1 payment which was greater than 1000:

SELECT Customers.FullName FROM Customers
WHERE Customers.Id IN
(SELECT Payments.CustId FROM Payments
WHERE Payments.Amount > 1000)
;

So i get my list of customers names, with 1 column, without duplicates.

I would like to get one more column, showing right the Amount which made the subquery condition true for that customer.

I don' t care whether for a given customer there it was more than 1 record in Payments having an Amount > 1000: i just want to show the one which made the subquery condition true.

How do i do that ?

Thanks in advance.

 

[rac2]

SELECT Customers.FullName, MIN(b.Amount)
FROM Customers
JOIN (SELECT Payments.CustId, Payments.Amount FROM Payments
     WHERE Payments.Amount > 1000) b
 ON Customers.CustID = b.CustID
GROUP BY Customers.FullName

 

[rac2]

SELECT Customers.FullName, MIN(Payments.Amount)
FROM Customers
JOIN Payments
ON Customers.CustID = Payments.CustID
WHERE Payments.Amount > 1000
GROUP BY Customers.FullName

 

[dosview]

Thanks. I had used a subquery because it was an excerpt of a more complex task where subqueries are used. You did put me on the right track.