SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-28-2008, 05:22 AM
| ||||
| ||||
 group by & left join 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.      |
Linear Mode
