[ MySQL combine the result of 3 select queries ]
I'm having trouble with a supposedly simple select query in MySQL, here's the scenario: I have 3 tables 'Student', 'PresenceRecords1' and 'PresenceRecords2' with the following data,
*Student
╔════╦══════╦═══════════╦══════════╗
║ id ║ sex ║ firstname ║ lastname ║
╠════╬══════╬═══════════╬══════════╣
║ 1 ║ masc ║ john ║ doe ║
║ 2 ║ fem ║ lisa ║ simpson ║
╚════╩══════╩═══════════╩══════════╝
*PresenceRecords1
╔════╦════════════╦════════════╦════════╗
║ id ║ 1student_id║ date ║ points ║
╠════╬════════════╬════════════╬════════╣
║ 1 ║ 1 ║ 2014-01-01 ║ 1 ║
║ 2 ║ 2 ║ 2014-01-01 ║ 1 ║
║ 3 ║ 1 ║ 2014-01-02 ║ 5 ║
║ 4 ║ 2 ║ 2014-01-03 ║ 1 ║
╚════╩════════════╩════════════╩════════╝
*PresenceRecords2
╔════╦════════════╦════════════╦════════╗
║ id ║ student_id ║ date ║ points ║
╠════╬════════════╬════════════╬════════╣
║ 1 ║ 1 ║ 2013-01-01 ║ 10 ║
║ 2 ║ 1 ║ 2011-02-01 ║ 1 ║
║ 3 ║ 1 ║ 2014-02-02 ║ 5 ║
║ 4 ║ 2 ║ 2014-03-02 ║ 15 ║
╚════╩════════════╩════════════╩════════╝
I'm supposed to query the tables and got the following result:
╔═══════════════════╦══════════════════╦═══════╦══════════════╗
║ student_firstname ║ student_lastname ║ score ║ lastpresence ║
╠═══════════════════╬══════════════════╬═══════╬══════════════╣
║ john ║ doe ║ 22 ║ 2014-02-02 ║
║ lisa ║ simpson ║ 17 ║ 2014-03-02 ║
╚═══════════════════╩══════════════════╩═══════╩══════════════╝
this is the query I'm using:
SELECT
s.first_name as student_firstname,
s.last_name as student_lastname,
SUM(pr1.score + pr2.score) as prpoints,
MAX(pr1.date) as maxdatepr1,
MAX(pr2.date) as maxdatepr2
FROM
student AS s
INNER JOIN
PresenceRecords1 AS pr1 ON s.id = pr1.1student_id
INNER JOIN
PresenceRecords2 AS pr2 ON pr1.1student_id = pr2.student_id
group by s.id
and I'm getting this result:
╔═══════════════════╦══════════════════╦══════════╦════════════════════════════════╦═════════════════════════════════╗
║ STUDENT_FIRSTNAME ║ STUDENT_LASTNAME ║ PRPOINTS ║ MAXDATEPR1 ║ MAXDATEPR2 ║
╠═══════════════════╬══════════════════╬══════════╬════════════════════════════════╬═════════════════════════════════╣
║ john ║ doe ║ 50 ║ January, 02 2014 00:00:00+0000 ║ February, 02 2014 00:00:00+0000 ║
║ lisa ║ simpson ║ 32 ║ January, 03 2014 00:00:00+0000 ║ March, 02 2014 00:00:00+0000 ║
╚═══════════════════╩══════════════════╩══════════╩════════════════════════════════╩═════════════════════════════════╝
Can someone point me in the right direction?
EDIT: Sorry for the long post, here is a SQLFIDDLE of my problem.
Answer 1
You can use UNION
,but i ask you to change your schema if you can store the data in one table for presence records rather saving them in individual table
SELECT
s.first_name as student_firstname,
s.last_name as student_lastname,
SUM(pr.score ) as prpoints,
MAX(pr.date) as maxdatepr1
FROM student AS s
JOIN
(
SELECT 1student_id sid,score,date FROM PresenceRecords1
UNION
SELECT student_id sid,score,date FROM PresenceRecords2
) pr ON(pr.sid =s.id)
group by s.id
Fiddle Demo
Answer 2
SELECT s.id,s.sex,s.first_name,s.last_name, SUM(x.score),MAX(x.date) FROM student s
JOIN
(SELECT * FROM presencerecords2
UNION
SELECT * FROM presencerecords1
) x
ON x.student_id = s.id
GROUP BY s.id;