Okay first thing I should let you know is I am using the Smarty Template engine... Now for this site I am working on I've got 2 tables.. one is advisors and one is recommendations. They both have quite a bit of the same information... Here is my query I have right now
<?php $data = $turbo->get_all("SELECT advisors.fname, advisors.lname, advisors.advisor_id, count(recommendations.rec_id) as tot_rec, avg(recommendations.total_score) as avg_score, recommendations.total_score FROM advisors INNER JOIN recommendations ON advisors.advisor_id = recommendations.advisor_id GROUP BY advisor_id ORDER BY advisors.lname"); ?>
Now this works for the most part -- but right now there is 21 entries in the advisors table and this is only picking up 14 of them.. Also something new I have to figure out is...
In the Advisors table there is a list of advisors at different institutions..
So say there is a John Smith at University of Marylyn and a John Smith at University of Southern California.. In the output I need to have it like
Smith, John University of Marylyn
Smith, John University of Southern California
So that the recommendations are not combined for the advisor.
Any help on this would be grateful.. Thanks in advance.