sql + asp join problem

[gnom]

I've just build a database in Access 2003 and uploaded it to my account on Brinkster.
There are two tables:

Referee:
PK id int
country_id int
name char
.....

Country:
PK id int
name char
.....

There is a relation between Country.id and Refree.country_id

But now i have a problem: a simple query works ok
sSQL = "SELECT * FROM Referee"

but not with JOIN:
sSQL = "SELECT * FROM Referee JOIN Country ON Referee.country_id=Country.id"

it doesn't work - there is an syntax error.

What is wrong? Database is for sure good, relation is defined well.

Suppose that this query works - how can I display the name of country?

Set oRS = oConn.Execute(sSQL)
...oRS("Country.name").Value...

...will this work?

Please help, It is my first asp + sql project. My Brinkster account doesn't support MS SQL- I have to use Access...

Thanks in advance

[ScaryBinary]

Been awhile since I used Access, but I think you have to user INNER JOIN, like:sSQL = "SELECT * FROM Referee INNER JOIN Country ON Referee.country_id=Country.id"Give that a shot. Other than that, the SQL looks fine.

To get the value of a field in a recordset, useoRS.Fields("name").Value

I think what you had will work, too. You might need to do what you did if you have two fields with the same name...oroRS.Fields("Country.Name").Value might work, too.

I'm a little rusty on the Access...:p

[gnom]

INNER doesnt work, I tried equivalent:

SELECT * FROM Referee, Country WHERE Referee.country_id=Country.id

and it works!

So is there any JOIN statement in Access or do i have to use above...

[ScaryBinary]

Hmmm...I have Access 2000 and INNER JOIN works fine....

Here's my query:SELECT * FROM Country INNER JOIN Referee ON Country.ID = Referee.CountryID;
and here were the results (with some fake sample data):
Country.ID Country.Name Referee.ID CountryID Referee.Name
-------------------------------------------------------------
1 USA 1 1 Bill
2 France 2 2 Jaque
1 USA 3 1 Frank

According to Microsoft, INNER JOIN should work. Make sure you have your relations set up properly to support the join....

Linky to some SQL help: Access 2003 INNER JOIN help (http://office.microsoft.com/en-us/assistance/HP010322411033.aspx)

[gnom]

well, you are right!
On my Brinkster control panel I have a module for testing query and
select * from Referee INNER JOIN Country ON Country.id=Referee.country_id
did work.

I found an error in ASP script...
Thanks for helping me!!