I need to code a database that stores possible moves in a board game.
I have a table that has two columns plus a numeric primary key, the columns being 'squareID' and 'possiblemoves', ie
With me so far? Right, I need to find out each pair of squares where you are allowed to move directly from the first to the second and also the second to the first, ie in the above table I'd get the pair A1, A2. Any idea how I'd construct a query to tell me this?[/code]
SELECT possiblemoves from <table name> WHERE squareID = 'A1';
Or am I not getting you? I dont think I am. What do you mean by "also the second to the first"? Your example doesn't say what square you are basing the example on...?
Oh, and do your own homework
"All our beliefs are being challenged now, and rightfully so. They're stupid." - Bill Hicks My Travels Blog
No, he wants to select records where squareID & possiblemoves = possiblemoves & squareID. i.e. A1 > A2 and A2 > A1. And then presumably only return one set of records (A1,A2).
Ping, round one to Kajun. It's not homework, it's far too advanced for our course so far . I thought of a way to do it by using subjoins or by creating a new table from the main table with the order possiblemoves and squareID reveresed and intersecting it with the main table.
Actually, we've got to use Oracle SQL+ for our course, which is bloody annoying as I'm used to MySQL syntax and there're subtle differences which leave me banging my head on the keyboard when trying to debug errors - simple stuff like distinct instead of unique etc. Anyone know if there's a way of getting MySQL to accept Oracle SQL+ commands? I don't want to install Oracle on my machine just to practise SQL+.