0

Let's say i've got a database with 4 tables:

  1. users: (personal database)
    • id
    • user_name
  2. lessons: (lesson names like math / gym etc.)
    • id
    • lesson_name
  3. payments (contains payments with lesson_id and a part of the payment [the whole payment is divided to 12 parts])
    • id
    • lesson_id
    • part
    • value
  4. groups: (to array users to lessons)
    • id
    • lesson_id
    • user_id

My question is how to query the database to get a table with list of all people attending to for ex. math (or any other lesson_id) with their name (probably LEFT JOIN) and SUM of all the payments for each period. The table header for chosen lesson_id should look like:

username | payment 1 | payment 2 | ...(3-11)... | payment 12

I have no clue how to make this work. Maybe my MySQL approach is innapropriate or i should divide it to more queries for more flexibility.

Thank You for your time!

check123
  • 1,989
  • 2
  • 22
  • 28
bukzz
  • 9
  • 1
  • You are asking for the SUM of all payments for a user_lesson but then you say you want a column for each individual payment. So.. which do you want or do you want both? – Matt Mar 22 '12 at 13:51
  • 1
    Confusing...your table header does not include a column for the SUM you requested. – D'Arcy Rittich Mar 22 '12 at 13:57
  • Sorry, if the question was not clear enough - im from Poland, my English is not so good :) In the payments table i've got a `part` column that contains a number (1-12) describing the part of payment. Users can pay in periods, that's why i want to know which part of the payment it is and sum it so i've got a table with username and sum of each payments for separate periods. It should be like _user_=>user_name ; _payment1_ => sum of all payments from `payments` where `part` = 1 ; ...etc for all the parts. – bukzz Mar 23 '12 at 13:23

1 Answers1

0
select users.id, users.user_name,lessons.id, lessons.lesson_name,
    (SELECT SUM(value) FROM payments
     WHERE payment.lesson_id = lessons.id)
FROM users
INNER JOIN groups ON (groups.user_id = users.id) 
INNER JOIN lessons ON (lessons.id = groups.lesson_id)
WHERE .....
Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123
Craig Trombly
  • 464
  • 2
  • 9