help with converting query to use inner joins

[gkellymail]

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]

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.developersdex.com ***

[Marshall Barton]

gk********@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]