MS Access database support - help with converting query to use inner joins

Author

help with converting query to use inner joins

gkellymail@gmail.com

2006-04-07, 1:31 pm

the following query works fine:

select link.idx, link.x_table, link.x_id_a, link.x_id_z, a.strandid,
b.strandid
from link_detail, link, strand A, strand B
where link_detail.x_table = 'enclosure' and link_detail.x_id = 3 and
link.idx = link_detail.linkidx
and A.strandid = link.x_id_a and B.strandid = link.x_id_z

would someone please convert this to a more efficient query using inner
joins please.

thanks.

Rich P

2006-04-07, 8:27 pm

select t1.idx, t1.x_table, t1.x_id, t1.x_id_z, a.strandID, b.strandID
From (link_detail t1 join strandA a on t1.idx = a.strandID) join strandB
b On t1.idx = b.strandID

Rich

*** Sent via Developersdex http://www.droptable.com ***

Marshall Barton

2006-04-07, 8:27 pm

gkellymail@gmail.com wrote:

>the following query works fine:
>
>select link.idx, link.x_table, link.x_id_a, link.x_id_z, a.strandid,
>b.strandid
>from link_detail, link, strand A, strand B
>where link_detail.x_table = 'enclosure' and link_detail.x_id = 3 and
>link.idx = link_detail.linkidx
>and A.strandid = link.x_id_a and B.strandid = link.x_id_z
>
>would someone please convert this to a more efficient query using inner
>joins please.

I think this is the equivalent query using joins:

SELECT link.idx, link.x_table, link.x_id_a, link.x_id_z,
A.strandid, B.strandid
FROM ((link_detail
INNER JOIN link
ON link.idx = link_detail.linkidx)
INNER JOIN strand A
ON A.strandid = link.x_id_a)
INNER JOIN strand B
ON B.strandid = link.x_id_z
WHERE link_detail.x_table = 'enclosure'
AND link_detail.x_id = 3

I doubt that it will be much faster though. I'm not sure
but Access may actually translate the JOINs to the form you
had before. Either way the key to making these queries run
faster is to index the linking and criteria fields.

--
Marsh
MVP [MS Access]

Sponsored Links