Solved

basic mysql query question

Posted on 2006-11-30
2
292 Views
Last Modified: 2009-07-29
I need help formulating a query to return results based on three tables I am using to store data relating to film box office revenue. (I’m not sure it is possible to put this in one query, in which case some alternative will be necessary.)

The three tables and their relevant columns are:

1) films (This table stores US box office gross data for films.)
      a.      film_id
      b.      title
      c.      box_result (the film’s US box office gross)
      d.      close_date (date the box_result was posted)

2) estimates (This table stores US box office gross estimates given by users.)
      a.      user_id
      b.      film_id
      c.      estimate

3) friends (This table stores data regarding who each user’s associates are.)
      a.      fan_id (the user_id of the user indicating someone is his friend)
      b.      star_id      (the user_id of the user so indicated)

I have stored a .sql dump of the test db here: http://infinidex.com/film_sample.sql

For the logged in user $_SESSION[user_id] I wish to return the following data for each film_id for which the user has provided an estimate (ie, there is a match between the user’s user_id and the film’s film_id in the estimates table):

1)      title
2)      box_result
3)      estimate provided by the user for this film
4)      % error of the user’s estimate [user_percent_error]
5)      the total number of estimators for this film [total_estimators]
6)      the average estimate provided by all users for this film [average_estimate]
7)      % error for the average estimate [average_percent_error]
8)      the user’s rank among all estimators for this film [rank]
9)      the user’s percentile rank among all estimators [percentile_rank]
10)      the average estimate provided by the user’s friends [friend_estimate]
11)      the total number of friends who provided an estimate for this film [total_friend_estimates]
12)      % error for the friends estimate [friend_percent_error]
13)      the user’s rank among his friends for this film [friend_rank]
14)      the user’s percentile rank among his friends [friend_percentile_rank]

ordered by close_date descending.

This would be the ideal format for the query, but if it is not possible to do this, or it would result in a very slow query, I am open to suggestions on alternatives. For example, I could store the user’s rank for each estimated film in the estimates table (calculated in advance), and the total number of estimators for a given film in the films table (also calculated in advance), which would mean that the query would require no rank calculations.

Thanks for your help.
0
Comment
Question by:bitt3n
2 Comments
 

Author Comment

by:bitt3n
ID: 18055950
right now I have this divided into one query per film, so if I display 50 films on the page I will have to do 50 queries per page, which presumably is much worse than doing one big query. Is this actually true or is it something I don't need to be worried about?
0
 
LVL 35

Accepted Solution

by:
Raynard7 earned 500 total points
ID: 18091523
Performance wise it would be faster to run one large query (50 records) than 50 individual records, you would need to hit each table and index so many less times by doing it in one query where the query engine would probably only look at each table a few times.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question