Can Access generate a.ID=b.ID (not INNER JOIN) in SQL?

[whit0313]

I'm doing some queries in Access Design view, then using the generated SQL elsewhere.

Unfortunately, when Access generates SQL, the joins are all INNER JOINs, not simply "where table1.id = table2.id".

For example, I get this:

SELECT Dept.DeptID, Dept.Name, Staff.Surname
FROM Dept INNER JOIN Staff ON Dept.ManagerID = Staff.StaffID;

where I'd rather get this:

SELECT Dept.DeptID, Dept.Name, Staff.Surname
FROM Dept, Staff 
WHERE Dept.ManagerID = Staff.StaffID;

The generated SQL joins get much more messy when you have three or four tables joined with INNER JOINs.

Does anyone know how to get Access Design View to generate "normal" SQL for these joins? - Thanks!

 

[r937]

three or four tables joined with INNER JOINs is "much more messy" to you?

i think the mishmash of different table join conditions all jumbled together in the WHERE clause along with filter conditions is much more messy

learn JOIN syntax, it is better for several reasons

and no, i don't know of any way to get access to generate the older, deprecated style of joins



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[PHV]

generate "normal" SQL for these joins
The syntax generated IS the normal join syntax.

 

[BNPMike]

Note Jet does not treat old style SQL in the same way as new style eg in updatability of queries. I would always recommend you stick to Access SQL - indeed as far as possible let Access write your SQL for you.

 

[cheerio]

There are no advantages in the WHERE syntax and potential major disadvantages. In particular, it is equivalent to asking Access to build a cross join and then test each row which is inefficient and could be unstable. eg if you have 1000 rows in each of two tables Access could build 1000000 rows being all combinations and then sift them.

 

[whit0313]

OK, I can see that INNER JOIN is the preferred method for pretty much everyone, and I take cheerio's point about the potential for 1,000,000 row tables. But, the people I work with all use a.ID=b.ID joins, and I'm expected to follow suit.

So, with due acknowledgement that it's not a reccommended thing to do, does anyone know how to tweak the way Access generates SQL?

 

[PHV]

does anyone know how to tweak the way Access generates SQL
By writing yourself the FROM and WHERE clause ...
Just a question if I may ask:
how the people you work with write outer joins ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[r937]

i would simply ask the people you work with how they do it with access -- maybe they know how

if they don't know how, could they be using something other than access?

i'd also like to know how they write left outer joins

in any case, converting from JOIN syntax to the old style in a text editor is fifteen seconds, twenty tops

i've re-formatted people's sql in a text editor thousands of times just simply to post sample sql on these forums, and the difference in syntax is minimal



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[r937]

heh, simultaneous thinking

;-)

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[whit0313]

does anyone know how to tweak the way Access generates SQL
By writing yourself the FROM and WHERE clause ...

Fair 'nuff. I figured that was probably the only way - thanks anyway though.

Just a question if I may ask:
how the people you work with write outer joins ?

Generally it's something along the lines of:

select fields from a,b
where a.ID = b.ID
and not exists (
  select * from c
  where c.ID = a.ID
)

 

[whit0313]

The other thing - most of the people I work with use hand-coded SQL, not Access. Though some use MS Query, which seems to do a.ID = b.ID joins.

 

[PHV]

In MS Query when you double_click the line joining the table's box and choose the 3rd option, the generated code is something like this:
SELECT clt.clt_cod, civ.civ_lon, clt.clt_nom
FROM {oj phv.clt clt LEFT OUTER JOIN phv.civ civ ON clt.civ_cod = civ.civ_cod}

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244