I need to write a query that joins 3 tables, a master with 2 detail tables
and I need to end up with 1 row per each row of the master table. The tables
are students, previous_institutions, and test_scores. So what I want is the
first previous institution and the average of the scores.
table student:
sid int auto_increment,
studentid varchar(11),
last_name varchar(30,
[...]
Table previous_institutions:
pid int auto_increment,
studentid varchar(11)
college varchar(50)
[...]
Table test_scores:
tid int auto_increment,
student_id varchar(11)
score int,
[...]
SELECT S.studentid, S.last_name,
P.college, avg(T.score)
FROM students S
LEFT JOIN previous_institutions P USING (studentid)
LEFT JOIN test_scores T USING (campus_id)
GROUP BY S.campus_id
This query actually works but it makes me nervous because I believe it
wouldn't even be legal in Oracle, for example. The P.college column is
neither in the group by clause nor in an aggregate function.
How can I make sure it selects the "first" ro from the previous_institutions
table for each student? "First" would be the one with the lowest pid.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
