Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Need some assistance cleaning up a query.Helpful Member! 

ochaos (Programmer) (OP)
26 Jul 11 8:16
Hello,

Currently, I have a couple of queries that are taking a couple seconds to process and load the page and I was wondering if you could point me in the right direction to clean this up.

I have two tables that are being accessed over 2 queries right now.

This query grabs all the beer names for one brewery

CODE


SELECT beer.beer_id,beer.beer_name
FROM beer
WHERE beer.brewery_id='some_id'
ORDER BY beer.beer

Then I take those results and loop through them to also display some stats about the beer.  (number reviews, average score etc.)

CODE

foreach($result do this){
   SELECT AVG(total) AS score, COUNT(beer_id) AS review_count   ,MAX(total) AS high_score ,MIN(tot) AS low_score
   FROM `ratings`
   WHERE beer_id='".$row->beer_id."'
}

I was trying to use a JOIN like so, but as soon as I do, it limits the results to one row.

CODE


SELECT beer.beer_id,beer.beer_name,AVG(total)
FROM beer
JOIN ratings ON ratings.beer_id=beer.beer_id
WHERE beer.brewery_id='some_id'
ORDER BY beer.beer

Is it possible to do what I am trying to do with one query?  I assume it will cut my page load time down significantly if I can consolidate it.

Thank you
 
ochaos (Programmer) (OP)
26 Jul 11 12:43
I just noticed that when typing this to clean it up, ORDER BY in both queries should be ORDER BY beer.beer_name
feherke (Programmer)
26 Jul 11 12:58
Hi

What is your goal ? If you want to get a list of all bears together with each bear's rating statistics, then you should use group by on beer.brewery_id and beer.beer_name and no where condition.
 

Feherke.
http://free.rootshell.be/~feherke/

Helpful Member!  r937 (TechnicalUser)
26 Jul 11 13:50

CODE

SELECT beer.beer_id
     , beer.beer_name
     , AVG(ratings.total) AS score
     , COUNT(*) AS review_count   
     , MAX(ratings.total) AS high_score
     , MIN(ratings.total) AS low_score    
  FROM beer
INNER
  JOIN ratings    
    ON ratings.beer_id = beer.beer_id
 WHERE beer.brewery_id = some_id
GROUP
    BY beer.beer_id
ORDER
    BY beer.beer_name

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

ochaos (Programmer) (OP)
26 Jul 11 14:00
Thanks for the replies.  r937's query gets me most of the way there, it's not displaying the beer names for the beers without reviews on them, but I will be able to tweak this query to include those when I have a couple minutes.

Thank you, I didn't think to group the results, my understanding of it was a little off it appears.

Thanks again!
r937 (TechnicalUser)
26 Jul 11 14:53

Quote:

it's not displaying the beer names for the beers without reviews on them
use LEFT OUTER JOIN instead, and change COUNT(*) to COUNT(ratings.beer_id)

smile

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

ochaos (Programmer) (OP)
26 Jul 11 16:17
Awesome, that does it, thanks again!

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close