MYSQL Convert join to subquery

I'm try to convert a join query to not have the join and use subqueries only but for the life of me I can not figure out a way.

TABLES:

With these tables I'm trying to do the following: Using subqueries get the FirstName, LastName and registration paid of the attendees who have paid the most registration.

This query produces that result using a join.

SELECT FirstName, LastName, SUM(tblregistration.RegistrationPaid) AS `AmountPaid`
FROM tblregistration, tblattendees
WHERE tblregistration.AttendeeID = tblattendees.AttendeeID
GROUP BY tblregistration.AttendeeID 
ORDER BY `AmountPaid` DESC 
LIMIT 5

Is there a way to do this? Thanks.

Answers


This should work :

SELECT
  (SELECT FirstName FROM tblattendees WHERE tblregistration.AttendeeID = tblattendees.AttendeeID) AS `FirstName`,
  (SELECT LastName FROM tblattendees WHERE tblregistration.AttendeeID = tblattendees.AttendeeID) AS `LastName`,
  SUM(tblregistration.RegistrationPaid) AS `AmountPaid`
FROM tblregistration
GROUP BY tblregistration.AttendeeID
ORDER BY `AmountPaid` DESC
LIMIT 5

Need Your Help

jquery fade toggle div over another div when click?

javascript jquery html css

This is my test page and I want when the page is loaded everything is hidden. When I click on "About" text is fade in using fadeToggle(); but when I click on "My work" another text is fade in but u...

Why am I seeing different font sizes when printing a page from an IFrame?

asp.net javascript html css printing

I have a simple, table based HTML page with a very simple style sheet. I can open the page in IE7 and FireFox 3 and it looks exactly the same. I can print the page from both browsers and it looks e...