Solved

basic mysql query question

Posted on 2006-11-30
2
297 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

Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
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.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

696 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