How can I do this now?

[Leo Karl]

I'm learning SQL from a 1990 Gruber book (Understanding SQL) in which this
self-join used to work:

SELECT first.cname, second.cname, first.rating
FROM Customers first, Customers second
WHERE first.rating = second.rating;

When run under Access 2002 in gives a "Syntax error in From clause" which I
can't seem to correct, no matter what I try. Any idea how to make it work
in under the more current SQL implementations?

Thanks, Leo

 

[Jacinto Muchine]

Leo,
Syntax error is usually typos...
There are a couple of issues on your query:

First: You are not using the table "Customers" so better
remove it from the FROM CLAUSE.

Second: It seems that your normalisation is not up to
scratch. You have both fields "cname", and "rating" on
both tables.

Assuming, rating is a primary key on one of your tables
then remove "second.cname" or "first.cname".

This is how it would look:

SELECT first.cname, first.rating
FROM first, second
WHERE first.rating = second.rating;

Jacinto

 

[Steve Schapel]

Leo,

Try putting in the AS, i.e...
SELECT first.cname, second.cname, first.rating
FROM Customers AS first, Customers AS second
WHERE first.rating = second.rating

- Steve Schapel, Microsoft Access MVP

 

[Leo Karl]

I had tried the same thing Steve, even copied and pasted your suggestion.
No luck! -- "Syntax error in FROM clause" still occurs.

Could it have anything to do with the fact that my screen says "Access 2000
file format"?
Since the code came from a text book, I think it did work on earlier
versions, but not sure why it's causing trouble now.

Leo

 

[Van T. Dinh]

My guess is that "first" is a reserved word in Access SQL and somehow it
messes your SQL String even though the reserved word "first" is used
differently from what you have in your SQL.

Try:

SELECT C1.cname, C2.cname, C1.rating
FROM Customers C1, Customers C2
WHERE C1.rating = C2.rating;

Actually, Join is a lot more efficient than criteria in WHERE clause so you
should use:

SELECT C1.cname, C2.cname, C1.rating
FROM Customers C1 INNER JOIN Customers C2
ON C1.rating = C2.rating;