how get records of a table as columns of other table for report

I have 4 tables:

users

id	name
1	a
2	b

products

id	name
1	aC
2	bC

bought

id	user_id	product_id	amount
1	1	1	100
2	2	1	200

sold

id	user_id	product_id	amount
1	1	1	100
2	2	2	200

Report Now I want to have a query to get below report:

user_id	user_name	bought_aC	bought_bC	sold_aC	sold_bC
1	a	100
2	b	100			200

Description

I want to have list of users with bought and sold amount of products.

Each user has one row in report. each product has two column for one for bought , one for sold.

My products are limited, so I want to have different columns for each product(each product has bought and sold column that show amount)

Is this possible?

I would appreciate if any one help me.

Answer

You are aggregating along two dimensions, so you need to aggregate before joining:

select u.id, u.name, 
       b.bought_aC, b.bought_bC,
       s.sold_aC, s.sold_bC
from users u left join
     (select b.user_id,
             sum(case when pb.name = 'aC' then b.amount end) as bought_aC, 
             sum(case when pb.name = 'bC' then b.amount end) as bought_bC    
      from bought b join
           products pb
           on b.product_id = pb.id
      group by b.user_id
     ) b
     on u.id = b.user_id left join
     (select s.user_id
             sum(case when ps.name = 'aC' then s.amount end) as sold_aC,
             sum(case when ps.name = 'bC' then s.amount end) as sold_bC
      from sold s join
           products ps
           on s.product_id = ps.id
      group by s.user_id
     ) s
     on u.id = s.user_id
order by u.id