Help with a query

[radar]

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

[CODE]
<?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");
?>
[/CODE]

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.

[Barand]

Try a LEFT JOIN which will list advisors even if they have no recommendations

[code]SELECT a.fname, a.lname, a.advisor_id,
count(*) as tot_rec, avg(r.total_score) as avg_score, SUM(r.total_score) as total_score
FROM advisors a LEFT JOIN recommendations r
ON a.advisor_id = r.advisor_id
GROUP BY a.advisor_id
ORDER BY a.lname[/code]

Note that it's OK to put newlines in a query so you don't need a 60 inch monitor to view it ;)

PS as your group by includes id there should be no problem with 2 with same name

[radar]

I kinda figured this out...  It was running on a INNER JOIN before and when I put a RIGHT JOIN in it seemed to work... so thats what i'm sticking with for now...

Ya know im glad this site is around for me while I am at work (I am a developer by trade)...

[radar]

Now the only thing I have to figure out is how to seperate them.. so that the advisors are seperated by institution in the display...

note: you can check this out by going to http://www.recommendmyadvisor.com -- click on the button that says browse by advisor

[Barand]

To do that you need to include the institution in your SELECT then ORDER BY a.institution, a.lname.

When you process the results, check for when the institution value changes and output the new value

[radar]

Tomorrow when I go to work I'll have to open this scripting up and take a look and see if I can figure out how to do the second part -- see the problem is I am not the original programmer on this and I'm not understanding more than half of the code -- and using smarty isnt helping much either as ive never used it before...  so if i cant im sure i'll be making more posts here..  thanks for the help thus far...