SELECT 
(
SELECT Registration.[Guid] FROM Registration 
WHERE RaceGuid = r.Guid 
AND AccountGuid = a.Guid 
AND Registration.isPaid = 1
)
,r.Guid as RaceGuid
,evt.Guid as EventGuid
,ath.Guid as AthleteGuid
,c2.CodeName
,c3.CodeName
,ur.IsPaid
,ur.IsPaid
,CASE
	WHEN u.USATNUMBER LIKE '%[^-+ 0-9]%' THEN 0
	ELSE NULL
	END
,ur.IsPaid
FROM _UserRaceFee urf
INNER JOIN _Events e ON e.RaceID = urf.RaceID
INNER JOIN _Users u ON u.UserID = urf.UserID
INNER JOIN Account a ON a.UserID = u.UserID
INNER JOIN Race r ON r.RaceID = urf.RaceID
INNER JOIN _Codes c ON c.CodeID = urf.FeeID 
INNER JOIN [Event] evt ON evt.EventID = urf.FeeID AND evt.RaceGuid = r.Guid
INNER JOIN _Profiles p ON p.ProfileID = urf.ProfileID
INNER JOIN Athlete ath ON ath.ProfileID = p.ProfileID
INNER JOIN _UserRace ur ON ur.ProfileID = urf.ProfileID AND ur.UserID = urf.UserID AND urf.RaceID = ur.RaceID
INNER JOIN _Codes c2 ON c2.CodeID = ur.TShirtSize
INNER JOIN _Codes c3 ON c3.CodeID = ur.Division
WHERE urf.RaceID = 146

quote:

---

Originally posted by visakh16

what are you trying to retrive inside subquery? will there be more than one record with isPaid = 1 in each RaceGuid AND AccountGuid group?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

SELECT RaceGuid, AccountGuid 
FROM Registration  
WHERE Registration.isPaid = 1
GROUP BY RaceGuid, AccountGuid 
HAVING COUNT(*) > 1