CODEWITHSUNDEEP
mysqljoinsumsubquery
RiCHiE
RiCHiE

Reputation: 288

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:

TABLE1TABLE2

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

Results

Is there a way to do this? Thanks.

Upvotes: 0

Views: 2409

Answers (1)

ForguesR
ForguesR

Reputation: 3618

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

Upvotes: 2

Related Questions