SQL JOIN on Sub Query and Only the First Row

1/27/2016 11:48:30 PM

The following will join the Users table to one row in the Donations by using a sub query. This will ensure that at least 1 row exists in the Donations table but will prevent multiple rows per user if they have more than just 1 donation. This will also return the most recent donation.

SELECT		*

FROM		Users
			--join on sub query, ensure at least one donation exists and get most recent donation
			JOIN Donations MostRecentDonation ON MostRecentDonation.Id =
			(	
				SELECT		TOP 1 Donations.Id
				FROM		Donations
				WHERE		Donations.UserId = Users.Id
				ORDER BY	Donations.Id DESC
			)