Help with a bit of easy SQL!

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.

Idea factory: Compare the box office theories with fact

Okay, so some folks are suggesting that summer box office was down because the movies were bad. Bereft of proof until recently, they’re now claiming vindication because box office receipts are now up, in a time when “better” movies are traditionally released (the fall).

So…how to find out what’s really happening? There’s no absolute way to know without either polling every single American or perhaps qualifiying intangible factors such as theater quality, pricing, and etc. (again that may involve signifigantly complicated polling.)

But what about this if you’re looking for a way to see if movie quality really matters:
Go to Rotten Tomatoes (or Metacritic) and tally up, then average the reviews of all movies released from May-August 2005. Then do the same for the same time period in 2004 (and perhaps even 2003). Then, compare the resulting years’ aggregate reviews with their box office numbers, and then you might have some fact to go with your conjecture.

My hypothesis? That movie quality and box office receipts are NOT corrollary. But hey, I could be wrong.

Help yourself figure out who owns the fish!

The other day I noticed this article on Coudal Partners. The article concerns a classic brain teaser that may or may not have been created by Albert Einstein. Here’s the gist:

There are five houses in a row in different colors. In each house lives a person with a different nationality. The five owners drink a different drink, smoke a different brand of cigar and keep a different pet, one of which is a Walleye Pike.

The question is– who owns the fish?

So I decided to whip up this quick little grid to help me work through the questions’ hints. It seemed to be an easier way than trying to keep track of things on paper or in a text file. It doesn’t have any cool actions to it (it won’t tell you if you’re right; fields won’t highlight or anything), but it’s 100% free to use/remix so if anybody out there can improve on it, go ahead.

Update: Thanks to Coudal for linking to it!

‘Who Owns the Fish?’ Helper

Don’t steal gasoline from this guy

From my local paper:

Brian Mitchell of Mitchell’s Sheet Metal estimated that about 300 gallons of gasoline had been stolen from his company’s trucks in the last few weeks, so he decided to do something about it.

On Monday, Mitchell set up an alert system in his parking lot and waited, gun by his side, in case something happened.

It didn’t take long. At 12:43 a.m. Tuesday, Mitchell surprised James Alan Waterman, 25, of Knox, and Tasha M. Vigneault, 19, of Freedom as they allegedly attempted to siphon gas from one of his trucks. They were held at gunpoint until sheriff’s deputies arrived to take the pair to jail.

Ode

Writer’s block, writer’s block, writer’s block, writer’s block, writer’s block, writer’s block, writer’s block, unoriginal thoughts, writer’s block, writer’s block.

Wriiiiiiterrrrrrr’s block.

Quote o’ the day

“For every six crappy poems you read, you’ll actually find one or two good ones. And that, believe me, is a very acceptable ratio of trash to treasure.”

-Stephen King