Skip to content
Advertisement

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.

Advertisement

Answer

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

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement