Checksum Arcanius

I ran into another dilemma in SQL-land today. Working on a money-management application, I had three tables that I wanted to gather information from. This normally leads to some relatively straightforward SQL:

SELECT account.name, contact.name, date, amount, type
FROM transactions, accounts, contacts
WHERE account_id = accounts.id
AND contact_id = contacts.id;

However, this returned results only for transactions that had both matching accounts and matching contacts, while what I wanted was a single record for each transaction, regardless of matching accounts or contacts. For two tables, this would be straightforward with a LEFT JOIN:

SELECT account.name, date, amount, type
FROM transactions LEFT JOIN accounts
ON account_id = accounts.id;

Which would return a row for every entry in the transactions table even if no matching account was found. I tried extrapolating that two three tables, like this:

SELECT account.name, contact.name, date, amount, type
FROM FROM transactions LEFT JOIN accounts, contacts
WHERE account_id = accounts.id
AND contact_id = contacts.id;

However, this didn’t do what I wanted, and I was getting multiple entries for each transaction. So I googled for help, and Google delivered: Left Joins to link three or more tables. From the information in this article, I was able to develop the SQL code that did exactly what I wanted:

SELECT `accounts`.`name` AS account_name,
`contacts`.`name` AS contact_name,
`date`, `amount`, `type`, `detail`, `memo`, `cleared`
FROM (`transactions` LEFT JOIN `accounts` ON `accounts`.`id` = `account_id`)
LEFT JOIN `contacts` ON `contacts`.`id` = `contact_id`
WHERE `parent_id` = 0

This returns a single row for each entry in transactions, even if matching accounts and contacts are not found. Esentially, it is a nested LEFT JOIN. First, MySQL left joins transactions and accounts, then it takes this result and similarly left joins contacts. This way, every row in the leftmost table (transactions) is preserved. I might have simply linked to the site, but I didn’t like the naming scheme in the examples on that site (crypic names like bdg and dom don’t earn accolades from me), and I came up with a better article title, “Nested”.

Saturday night, I attended Colin’s LAN party. I expected to make only a few hours’ appearance – it had started at noon, and I had things planned for the next day. Then I played Homeworld 2, and I stayed until 6:00 am, when I fell asleep, not to be cognizant again until about 11:30. In Homeworld 2, there is a bit too much going on to really get your head around it all, as you try to control hundreds of fighters, resource gatherers, factory ships, frigates, corvettes, destoryers, and battleships. For an even crazier trip, try out the Warlords modification, which esentially makes the game into a giant Star Wars battlefield and can easily overcome even the most powerful modern computers. Its great fun, and has, of course, rekindled my interest in a much grander scale strategy game idea that I’ve tossed around for sometime.

It has been quite a while since I saw most of the movies, but I thought I should write down what I remember before any more time goes by. This year’s SIFF was somewhat disappointing after several phenominal films I saw at last year’s. I will use a similar format and the same 1-5 scale for this years films, as best as I can remember:

Shake Hands with the Devil – Hallowing Documentary – Canada – 3
Fly Filmmaking – Bad Narratives – US – 1.5
Being Caribou – Political Documentary – Canada – 3
Night of the Living Dorks – Over-the-top Comedy – Germany – 3.5
The Warrior – Hallowing Drama – UK – 4
The Circus – Chaplin Comedy – US – 3

Most notably, there were no fives and only one four. I also learned that six movies is my upper limit – I had eight tickets but didn’t make it to some of the movies, so I won’t plan on making that mistake again.

Nearly 18 months into my “3-6 month” internship at Microvision, I continue to find ways to impress myself with my software testing prowess. Details must remain hazy, but let me just say that a test I developed continues to have exact correlation with a much more time-consuming test that has been used for much longer. Yet the best part of the job is the nearly constant learning opportunities that I encounter. The weeks without something to learn are very few and far between. Combined with almost limitless flexibility and a great group of people to work with, what more could I ask for? Well, other than a reverse in the trend of the stock price, hey?

I believe Chicago O’Hare may be my personal worst airport ever. In addition to having already spent Christmas Eve stranded there, I now had the great opportunity of turning a 45-minute excursion into an hour-and-a-half wait-a-thon while the plan that was supposed to arrive at 10:13 didn’t come in until well after 11:30. Thanks O’Hare, you putz.

Clarification: I picked my parents up at the airport last night. I was supposed to pick them up at 10:15, but it ended up being about 11:45. Their departure was delayed for 30 minutes due to electrical problems at O’Hare, then they missed their scheduled take-off and they had to wait another thirty minutes in the take-off line. When they made it to Sea-Tac, they then had the chance to wait on the tarmac for a while until their gate became open from the on-time plane that was using it. Finally, thier luggage took about thirty minutes to make it to the carousel. And I blame this all on Chicago.

Yesterday was a sunny and successful day. I woke up to my phone-alarm at 5:50 and showered, then had breakfast with Dan at Chaces’ Pancake Corral. After this, I headed home and switched the Highlander for the Truck and my bike. The next stop was Ian’s house for the TRC leadership meeting, which I thought was pretty productive. I had to leave that about 40 minutes early to make it to the South Bellevue Park & Ride, where I met up with Dan and biked West across the I-90 bridge. We made it to the Mercer Island-Seattle bridge deck around 11:40, and the Blue Angels arrived a bit after noon. The show was quite good – better than last year’s, if I remember correctly. It was fairly long – over 30 minutes – and displayed a good number of tricks, including four or five at the end of the show with all six planes working together. It was a good time. After the show, we ate at Subway on the island, then headed back to my truck which we took into Seattle to search for housing for me. We found a number of nice and a few sketchy places that I’m looking into, then enjoyed some Jamba Juice before heading back. I had dinner with the Marshes, excellent ribs by Jay and a great supporting cast including beans, vegies, salad, and bread by Kay. The Parkins were also in attendence, and the conversation was good. The night was wrapped up by watching fear.com, a pretty ridiculously bad movie, but, as I was explaining to Jason, not as bad as some films I have seen. It kept me up a little longer than I would have liked and then I compounded the problem when I stayed up and chatted online. The result is this – although I woke up at 5:50 to my phone alarm, I went back to sleep and only became cognisant around 9:00am. So that didn’t work so well. However, I should be back in my own bed tonight – when I visited yesterday to pick up some stuff, the toxicity part II was mostly dissipated, and the ventilated rooms, including mine, were quite nice to be in. Many thanks to the Marshes for letting me hole up here in the meantime. I enjoy avoiding affixiation.

The final touches went onto the wood floor Friday night. This time, I wised up and thanks to the generosity of some friends, have been able to sleep away from the fumes for a few nights.

A week and a day ago, I hurt my heel while landing after attempting to catch a Frisbee. I mostly stayed off the sore spot, even while running in additional games, and it seemed to be getting better. However, the last couple of days, I put a lot of stress on it again, as it wasn’t hurting as much, and now its hurting again. It may be time to bite the bullet and take a week off so I can get back to full strength sooner rather than later. As some people wiser than myself suggested, it might also be a good idea to get it xrayed (along with the pinky of my right hand, another lingering injury) if it doesn’t get better sometime soon.

Today, while playing ultimate, I took my shirt off, and burned. The last time I did this it led to a very unpleasant experience – so apparently, I didn’t learn the lesson yet, but hopefully, it isn’t as bad this time. Or anywhere nearly so.

As far as goals, I woke up today at 5:45ish, before my alarm (in the form of a phone) tried to wake me. Weird! But I’ll take it. I also called some landlords in Seattle, but talked only to recording machines.