SQL joins

For quick answers to quick questions on whatever subject you need!

Moderator: Thought Police

Locked
Zathras
Beta-Mandarin
Beta-Mandarin
Posts: 585
Joined: Mon Jan 13, 2003 12:21 am
Location: Oxford/Preston
Contact:

SQL joins

Post by Zathras »

Hi

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

Code: Select all

squareID possiblemoves
A1       A2
A1       B2
A1       C4
B1       A2
A2       A1
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]
DaBeeeenster
Beta-Mandarin
Beta-Mandarin
Posts: 893
Joined: Thu Sep 12, 2002 10:45 am
Location: Bondi Beach, Sydney ;)
Contact:

Post by DaBeeeenster »

Code: Select all

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 ;)
Image
"All our beliefs are being challenged now, and rightfully so. They're stupid." - Bill Hicks
My Travels Blog
Kajun
World Controller
World Controller
Posts: 10412
Joined: Tue Jan 22, 2002 12:00 am
Location: Hear.
Contact:

Post by Kajun »

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).
Kajun is awaiting approval.
Zathras
Beta-Mandarin
Beta-Mandarin
Posts: 585
Joined: Mon Jan 13, 2003 12:21 am
Location: Oxford/Preston
Contact:

Post by Zathras »

Ping, round one to Kajun. It's not homework, it's far too advanced for our course so far :roll:. 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.
Kajun
World Controller
World Controller
Posts: 10412
Joined: Tue Jan 22, 2002 12:00 am
Location: Hear.
Contact:

Post by Kajun »

Just like to add that I haven't a clue what to do, but would be interested to find out. Take it we can't use vB or owt like that?
Kajun is awaiting approval.
Zathras
Beta-Mandarin
Beta-Mandarin
Posts: 585
Joined: Mon Jan 13, 2003 12:21 am
Location: Oxford/Preston
Contact:

Post by Zathras »

It's very simple SQL. Surprised you don't know it Kaje.
Kajun
World Controller
World Controller
Posts: 10412
Joined: Tue Jan 22, 2002 12:00 am
Location: Hear.
Contact:

Post by Kajun »

Aye. Fuck u2.
Kajun is awaiting approval.
DaBeeeenster
Beta-Mandarin
Beta-Mandarin
Posts: 893
Joined: Thu Sep 12, 2002 10:45 am
Location: Bondi Beach, Sydney ;)
Contact:

Post by DaBeeeenster »

Er, cba to think about this properly, but you probably need to reference the tables twice...

so like

Select * from SquareID s1, SquareID s2, possibleMoves p1, possibleMoves p2...

You know that method?
Image
"All our beliefs are being challenged now, and rightfully so. They're stupid." - Bill Hicks
My Travels Blog
Zathras
Beta-Mandarin
Beta-Mandarin
Posts: 585
Joined: Mon Jan 13, 2003 12:21 am
Location: Oxford/Preston
Contact:

Post by Zathras »

Yes I do, but I've solved the problem anyway.
Zathras
Beta-Mandarin
Beta-Mandarin
Posts: 585
Joined: Mon Jan 13, 2003 12:21 am
Location: Oxford/Preston
Contact:

Post by Zathras »

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+.
Locked