SQL JOIN statement

[seen]

Hi gurus,

I have the following statement which works fine in access:

SELECT table1.test FROM table1 JOIN table2 ON table1.col = table2.col

I run into a problem when I do the following:



SELECT a.test FROM table1 AS a JOIN table2 AS b ON a.col = b.col

It throws a syntax error on the FROM claus.. Can anyone tell me what I'm missing? Thanks..

[RuralGuy]

Have you tried:
SELECT test FROM table1 AS a JOIN table2 AS b ON a.col = b.col

[seen]

Thanks for the response. I just tried it now; still doesn't work.

Not sure if it's important to note but the recordset is DAO.

[RuralGuy]

I just took a guess. You are Aliasing and I've not really tried that before. Maybe someone with more experience with SQL will drop by with a suggestion.

[seen]

Okay.. for anyone else viewing this, I'll also note that the below works:

SELECT a.test FROM table1 AS a, table2 AS b WHERE a.col = b.col

The issue is when I try to add in the "JOIN" clause with aliasing everything gets fubar'd.

[NTC]

just make it in normal query grid design view

then if you want the sql - change it to sql view

[seen]

The issue with using the query design grid is that it avoids the JOIN statement all together and defaults to the old syntax. ie. It'll generate the SQL as I stated in my previous post:

SELECT a.test FROM table1 AS a, table2 AS b WHERE a.col = b.col

I suppose it may not be possible to combine both JOIN and aliasing, but I'd like to confirm it's not an option.

[NTC]

?? a query is a query; the design via query grid can't "avoid" a join.

make it work in query grid design view.....then you'll have the sql that works and can be ported to a vb statement with minimal syntax tweeks.... I do it all the time....

[NTC]

let me add - - in some cases it is easier for me to conceive of the query via sql first - - and then I am interested in viewing it in grid view....just to see how it would be set up via grid view...

and in some cases the query can be so complex there is no grid view possible and Access won't go into grid view it just stays with sql text....

but you can add the same table twice; it will assign a default aliase that you can change in the sql text later if you want...so alot of queries can be set up in grid view.

[seen]

Let me clarify...

The two below statements should do the exact same thing:

1. SELECT a.test FROM table1 AS a, table2 AS b WHERE a.col = b.col

2. SELECT a.test FROM table1 AS a INNER JOIN table2 AS b ON a.col = b.col

However, the 2nd statement does not appear to work in Access. When I use the grid view it will only generate the 1st statement. What I want to know is whether the physical syntax "JOIN" can be incorporated in Access to work with aliasing.

I appreciate the responses thus far...

[NTC]

SELECT table1.test
FROM table1 INNER JOIN table2 AS b ON table1.col = b.col;