By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,667 Members | 2,261 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,667 IT Pros & Developers. It's quick & easy.

help with converting query to use inner joins

P: n/a
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.

Apr 7 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a

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 ***
Apr 7 '06 #2

P: n/a
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]
Apr 7 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.