I have three different tables for 3 different activities. The common field is user_id.
Performance:
id | user_id | date | mark
1 | 123 | xx | 20
2 | 456 | xx | 10
3 | 789 | xx | 5
4 | 123 | xx | 10
5 | 456 | xx | 10
6 | 789 | xx | 5
Internal Activities:
id | user_id | date | mark
1 | 123 | xx | 20
2 | 456 | xx | 10
3 | 789 | xx | 5
4 | 123 | xx | 10
5 | 456 | xx | 10
6 | 789 | xx | 5
Other Activities :
id | user_id | date | mark
1 | 123 | xx | 20
2 | 456 | xx | 10
3 | 789 | xx | 5
4 | 123 | xx | 10
5 | 456 | xx | 10
6 | 789 | xx | 5
How to fetch each user marks in all the 3 tables for and sort it in DESC. result should be like
id | user_id | mark
1 | 123 | 90
2 | 456 | 60
3 | 789 | 30
Thanks in advance
Try this:
SELECT p.id, p.user_id, SUM(p.mark) FROM Performance p
LEFT JOIN Internal Activities ia ON p.id=ia.id
LEFT JOIN Other Activities oa ON ia.id=oa.id
GROUP BY p.user_id;
SELECT tmp.ID,tmp.USER_ID,tmp.SUM(mark)
FROM
(select * from Performance
union all
select * from Internal_Activities
union all
select * from Other_Activities
) as tmp
group by tmp.USER_ID
Note :- Why are you keeping 3 separate table with same schema ,cant you make it one ?
SELECT
p.id
,p.user_id
,@performance:=p.mark AS performance
,@other_activities:=oa.mark AS other_activities
,@internal_activities:=ia.mark AS internal_activities
,@total_mark:=@performance+@other_activities+@internal_activities AS total_mark
FROM
table1 p
LEFT JOIN table2 ia
USING(user_id)
LEFT JOIN table2 oa
USING(user_id)