Hey, prepare for a bleg: I’m looking for an SQL pro to give me a hand with a bit of a tricky sql statement. An Amazon.com gift certificate for $10 (I know, big bucks!) goes to whoever can successfully help me with this issue.
Here’s the deal: I’ve got 3 tables in my database- users, directors, and movies. When my user logs in, I want to display their favorite movies, organized by director. An example of what a user would see when they log in:
Steven Speilberg
* AI
* Minority Report
* ET
Woody Allen
* Annie Hall
* Crimes and Misdemeanors
* Bullets Over Broadway
Now, the problem I’m having is that my attempts to pull both the directors, as well as their films, out of the database, are failing miserably. Instead of pulling one instance of each director along with a list of their movies beneath their name, right now I’m getting:
* One instance of the director’s name displayed for every movie in the movies table
Worse, I also get:
* A complete list of all movies from the movies table displayed under each director’s name (whether or not the movie is by that particular director).
So what I’m asking for (and what’ll score you that sweet Amazon gift card) is this:
* ONE QUERY to pull in the list of directors for a particular user, only outputting one instance of each director.
And:
* ONE QUERY to pull in the particular director’s movies (again, these have to be sorted to make movie.users_userID = current logged in user, because users can have different lists of “favorite movies by fav directors”).
If you can give me a hand writing a quick (but somewhat tricky) sql statement, I’ll be forever grateful. I’ll demonstrate my gratefulness with an Amazon gift card. Send your assistance to jgc-at-jasonclarke.org. Thanks in advance!
UPDATE: Contest over.
