Solved

basic mysql query question

Posted on 2006-11-30
2
286 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
Comment Utility
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
Comment Utility
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now