Solved

basic mysql query question

Posted on 2006-11-30
2
295 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
updating the date data 12 40
PHP & MySql - Turn Select Query Results into a Glyphicon 3 46
Mysql how to execute a commands file 5 33
MySQL programmer starter 25 29
Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

726 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