My brother and I have been working on a way to deal with the placement of starsystems and planets in Imperial Kingdoms. After figuring out we would create a table for quadrants and starsystems, we then had to go about finding out which inactive quadrant is next to a active quadrant. This was much easier said than done.
First he made a database query that would do all the work using subqueries. After he made a very elegant SQL query we ran it in a test table, only to find out it would not work. We modified it from here to kingdom come and still it would not work. We even checked the MySQL help files and, according to them, we should've been able to do it. We started to suspect there might be something with the version numbers, because we only had MySQL 4.0 and the documentation was for MySQL 5.0 alpha. We were starting to get close to the truth now, and a google search later found out MySQL didn't start doing subqueries until 4.1.
A moan and a quick bathroom break later, we set about finding an alternative method. We could still use JOIN queries, so we started to think about that. My brother wasn't sure JOINing a database table with itself would work, but figured it was worth a shot so I joined the table to itself and aliased them as 'a' and 'b'. Once we set all the needed clauses he said it would be funny if it worked. I copied it over, ran it, and, surprise surprise, it returned results. He laughed his butt off and exclaimed how dirty of a SQL statement it was.
So, I give you the dirty subquery-replacement SQL statement. Enjoy.
SELECT DISTINCT a.`quadrant_id` FROM `quadrants` a JOIN `quadrants` b WHERE a.`game` = 12 AND b.`game` = 12 AND a.`active` = '0' AND b.`active` = '1' AND ( a.`x` = b.`x` OR a.`x` = b.`x`-1 OR a.`x` = b.`x`+1 ) AND ( a.`y` = b.`y` OR a.`y` = b.`y`-1 OR a.`y` = b.`y`+1 ) ORDER BY RAND() LIMIT 1