Multiple table, multiple column query

Question

I have the following tables:

transactions : Transaction_ID Datetime Giver_ID Recipient_ID Points Category_ID Reason

rewards : Reward_ID Title Description Image_URL Date_Inactive Stock_Count Cost_to_User Cost_to_System

purchases : Purchase_ID Datetime Reward_ID Quantity Student_ID Student_Name Date_DealtWith Date_Collected

Staff members award students with points which becomes an entry into the transactions table, like this:

Transaction_ID Datetime Giver_ID Recipient_ID Points Category_ID Reason
8 2011-09-07 36761 127963 2 1 Excellent behaviour in behaviour unit

Students can then purchase a reward which becomes an entry into the purchases table, like this:

Purchase_ID Datetime Reward_ID Quantity Student_ID Student_Name Date_DealtWith Date_Collected
1570 2012-05-30 12:46:36 2 1 137616 Bradley Richardson NULL NULL

All of the rewards are stored manually in the rewards database table:

Reward_ID Title Description Image_URL Date_Inactive Stock_Count Cost_to_User Cost_to_System
1 Lunch Time Queue Pass (month) Beat the queue and get to the hot food early! /user/74/167976.png 2012-04-16 11:50:00 0 100 0

My question is this:

What SQL statement can I use to return Student Name, Points Earned, Points Spent, Points Remaining?

I asked a similar question a while back, which offered the following statement. However, upon inspection, it doesn't appear to be entirely accurate - specifically Points Spent isn't working correctly.

SELECT  Recipient_ID AS StudentID,
        SumOfPointsOfPurchasesMade.Points AS PurchasesMade,
        SumOfPointsEarned.Points AS PointsEarned,
        SumOfPointsEarned.Points - COALESCE(SumOfPointsOfPurchasesMade.Points, 0) AS CurrentPoints
FROM   
(
    SELECT SUM(Points) AS Points, Recipient_ID 
    FROM   transactions
    GROUP  BY Recipient_ID
) AS SumOfPointsEarned 
    LEFT JOIN 
    (
        SELECT purchases.Student_ID,  SUM(rewards.Cost_to_User) AS Points 
        FROM   purchases 
            INNER JOIN rewards 
            ON purchases.Reward_ID = rewards.Reward_ID
        GROUP  BY purchases.Student_ID
    ) AS SumOfPointsOfPurchasesMade 
    ON SumOfPointsEarned.Recipient_ID = SumOfPointsOfPurchasesMade.Student_ID
WHERE SumOfPointsEarned.Points < SumOfPointsOfPurchasesMade.Points
ORDER BY `CurrentPoints`  DESC

Thanks in advance,

vyegorov · Accepted Answer · 2012-07-04 11:25:38Z

3

You just need to use an outer join to combine the tables, then group by student:

SELECT purchases.Student_Name                               AS `Student Name`,
       SUM(transactions.Points)                             AS `Points Earned`,
       SUM(rewards.Cost_to_User)                            AS `Points Spent`,
       SUM(transactions.Points) - SUM(rewards.Cost_to_User) AS `Points Remaining`
FROM   transactions
  LEFT JOIN purchases ON purchases.Student_ID = transactions.Recipient_ID
  LEFT JOIN rewards   USING (Reward_ID)
GROUP BY purchases.Student_ID
ORDER BY `Points Remaining` DESC

edited Jul 4, 2012 at 11:25

vyegorov

23k7 gold badges61 silver badges76 bronze badges

answered Jul 4, 2012 at 11:23

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Slava Over a year ago

Good answer, but with MySQL it would be better to use INNER join where possible (outer ones often get REALLY slow). My point is change the joins to LEFT JOIN (purchases INNER JOIN rewards USING(Reward_ID)) ON /*...*/ as long as any purchase always has a connected reward row.

turbonerd Over a year ago

When I try to use your statement, my MySQL server times out... :) I don't know if it's because there's ~100,000 rows in the transactions table? Is there an alternative?

eggyal Over a year ago

@dunc: Do you have indexes on your join columns? Have you tried the optimisation suggested by Slava?

eggyal Over a year ago

@dunc: To be clear, you have indexes on purchases.Student_ID, rewards.Reward_ID and a compound index on transactions.(Recipient_ID, Reward_ID)?

turbonerd · Accepted Answer · 2012-07-04 11:57:02Z

I've stupidly realised that a tiny mistake from the other post fixes the problem I was having:

SELECT SumOfPointsEarned.Points - COALESCE(SumOfPointsOfPurchasesMade.Points, 0) AS CurrentPoints
FROM   
(
    SELECT SUM(Points) AS Points, Recipient_ID 
    FROM   transactions 
            WHERE Recipient_ID= 137642
    GROUP  BY Recipient_ID
) AS SumOfPointsEarned 
    LEFT JOIN 
    (
        SELECT purchases.Student_ID,  SUM(rewards.Cost_to_User * purchases.Quantity) AS Points 
        FROM   purchases 
            INNER JOIN rewards 
            ON purchases.Reward_ID = rewards.Reward_ID 
                    WHERE Student_ID = 137642
        GROUP  BY purchases.Student_ID
    ) AS SumOfPointsOfPurchasesMade 
    ON SumOfPointsEarned.Recipient_ID = SumOfPointsOfPurchasesMade.Student_ID
ORDER BY CurrentPoints ASC

This one line was the problem:

SUM(rewards.Cost_to_User * purchases.Quantity) AS Points

No multiplication of quantity. Doh!

Collectives™ on Stack Overflow

Multiple table, multiple column query

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related