Jump to content

Help with a query


radar

Recommended Posts

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.
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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)...
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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...
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.