Query designer ...

  • Thread starter Thread starter Fabio Piemontesi
  • Start date Start date
F

Fabio Piemontesi

Hi

Access appears to use a depth first search to build FROM
clause for multi-table queries when using visual query
designer. Can anyone confirm if this is indeed the case,
and perhaps provide more information on how depth first
search is applied - for example,

1. Is a directed query graph used, and if so, how is the
problem of which node to start on addressed?

2. How is actual FROM clause built, and how to get nested
parenthesis correct? For example, for the following mult-
table query, where for each Tx --> Ty, Tx is the ONE in
ONE to MANY relationship,

T0 --> T1 --> T2
| |
\/ \/
T5 --> T4 <-- T3
fig1. Table relationships

doing a depth first search, and adding '(<table>' to FROM
clause every time node representing <table> is discovered
and adding '<table>)' to FROM clause every time node
representing <table> is finished, you'll get something
like this -

(T0 J (T1 J (T4 T4) J (T2 J (T3 T3) T2) T1) T0)
(T5 T5)
fig2. Depth First Search Trees

Query designer in access gives you something link this -

T5 J ((( T0 J (T1 J T4 ON...) ON...) J T2 ON...) J T3
ON...) ON...
fig.3 SQL generated by access

Any ideas on how to go from Depth first search trees in
fig2. (if indeed DFS is actually used) to SQL in fig.3

Many thanks

Fabio Piemontesi
Cape Town, South Africa
 
Fabio-

You cannot influence how the Access JET engine solves a query by changing
the sequence of the tables in the FROM clause. The only way to influence
the plan is to create indexes on the JOIN key fields and perhaps fields that
you use in the WHERE, GROUP BY, and HAVING clauses.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Back
Top