1

I am having 3 tables in my MySQL DB. The Tables and columns are like in sample below

Table User

id, name,  dob,       gross_salary
----------------------------------
1,  test1, 12 Mar 90, 50000
2,  test2, 10 Jan 85, 45000

Table Wage

ida, date,      allowence_paid
------------------------------
1,   10 Jul 13, 12000
2,   10 Aug 13, 23000
2,   12 Aug 13, 1000
1,   15 Aug 13, 15000

Table Loan

id, date,      loan_amount
--------------------------
2,  05 Jul 13, 500
1,  05 Aug 13, 2500
1,  06 Aug 13, 1200

I need these three tables merged in results for Aug 13 like

id, name,  allowence_paid, loan_Amount, paid
--------------------------------------------
1,  test1, 15000,          3700,        11300
2,  test2, 24000,             0,        24000

SUM of two columns from two different tables joined to another table is required in my case.

Can I get help for the query? I have experimented as per MySQL JOIN with SUM and 3 tables and failed.

Community
  • 1
  • 1
user3001180
  • 11
  • 2
  • It's called `join`. You have those in a couple of flavors, of which `inner join` and `left (outer) join` are most commonly used. – GolezTrol Nov 17 '13 at 08:57
  • 3
    possible duplicate of [MySQL JOIN with SUM and 3 tables](http://stackoverflow.com/questions/11040948/mysql-join-with-sum-and-3-tables) – GolezTrol Nov 17 '13 at 08:59
  • You should know that even if people know the answer, they are reluctant to help when you have not shown any effort at all. And this feels like homework to me. We are here to HELP you, not to COMPLETE your homework. – MJB Nov 17 '13 at 15:32
  • I am expecting help from experts. Not assigning any home works to beginners. I have experimented as per link http://stackoverflow.com/questions/11040948/mysql-join-with-sum-and-3-tables and failed. – user3001180 Nov 18 '13 at 08:47

1 Answers1

0

This seems to work:

select *, allowence_paid-loan_amount as paid
from
(
select User.id as UserId, User.name as UserName, sum(allowence_paid) as allowence_paid
from Wage join User on Wage.ida=User.id 
and Wage.date between '2013-08-01 00:00:00' and '2013-08-31 23:59:00'
  group by UserId
) W JOIN
(
select User.id as UserId, User.name as UserName, sum(coalesce(loan_amount,0)) AS   loan_amount 
from Loan right outer join User on Loan.id=User.id 
and Loan.date between '2013-08-01 00:00:00' and '2013-08-31 23:59:00'
group by UserId
) L ON W.UserId = L.UserId

SQL fiddle here.

neutrino
  • 2,297
  • 4
  • 20
  • 28