Join Queries

[craigmanutd]

Hello everyone..

Edited January 31, 2013 by craigmanutd

[justsomeguy]

SELECT f.*, c1.Clubname AS home_name, c2.Clubname AS away_name FROM fixture AS f INNER JOIN clubs AS c1 ON f.homeclub=c1.clubId INNER JOIN clubs AS c2 ON f.awayclub=c2.clubId

[craigmanutd]

That's massively appreciated ill try it when I get home in a few hours and let you know how it goes!Thankyou!!!

[craigmanutd]

Ok, I'm home. I've tried that and various alterations on that thing you posted but it seems to be giving me an error at the first "AT f" bit, where it says "SQL command not ended properly". This is pretty much the same error message I've been running into all along, any ideas? (I did put the semi colon on the end of the statement by the way haha!)

[justsomeguy]

There's no "AT" in the query I wrote.

[craigmanutd]

Sorry thats my typo it should have said AS. the AS f bit just before INNER JOIN clubs. Sorry about that!!

[justsomeguy]

That syntax should work in most databases. Maybe look up the documentation for the database you're using about how to create a table alias, that's what "f" and "c1" are. Or maybe it expects different join syntax.

[craigmanutd]

Ok, its SQL Developer for Oracle I'm using so I'll have a look. Thanks for that, its appreciated anyway at least I'm closer now!

[craigmanutd]

Right, this isn't as simple as I'd hoped. It seems no matter what I try with this it continues to give the "SQL command not properly ended" error. The closest I've got to achieving what I want at this stage (without errors that is), is using the query: SELECT f.fixtureId, c.clubName, c.clubNameFROM fixture f JOIN club cON (f.homeClub = c.clubId OR f.awayClub = c.clubId); This returns the fixtureId, homeClub and awayClub but not the names of the clubs, as I need. Any more ideas from somebody a bit more clued up than me lol? Edit: Good news, I have managed to get it working by doing as justsomeguy said and joining it twice instead of just the once... i.e. I figured out what the aliases were doing so once I knew that I managed to figure out where it was going wrong! Thanks for your help!!

Edited January 31, 2013 by craigmanutd

[justsomeguy]

You need to join the clubs table twice, check my example above. c1 is the copy of clubs that one name is coming from, and c2 is the other copy. You're only joining once so it's only going to get one name from that table.

[craigmanutd]

Ok, what is it that the f.* at the start is doing in your statement? On entering SELECT f.*, c1.Clubname AS home_name, c2.Clubname AS away_name FROM fixtures AS f INNER JOIN clubs AS c1 ON f.homeclub=c1.clubId INNER JOIN clubs AS c2 ON f.awayclub=c2.clubId; I'm still getting the error at AS f about the command not ending properly. At this stage I've also waded through this Oracle SQL book I've got here and I've got to say its got me stumped has this!

[justsomeguy]

That's telling it to select all columns from the table "f", where "f" gets defined as an alias for fixtures. You could also just use the full table names, the aliases are just there to make it shorter:

SELECT fixtures.*,c1.Clubname AS home_name,c2.Clubname AS away_nameFROM fixturesINNER JOIN clubs AS c1ON fixtures.homeclub=c1.clubIdINNER JOIN clubs AS c2ON fixtures.awayclub=c2.clubId;