?
Solved

more efficient sql statement?

Posted on 2003-03-28
18
Medium Priority
?
170 Views
Last Modified: 2012-06-27
is there a more efficient way to do this?

i cant seem to work out the join accros the tables to show the value pollques in pollquestions

strSQL = "SELECT DISTINCT " & tablekeywords & ".id,pollrelate.pollid FROM " & tablekeywords & " INNER JOIN pollrelate ON " &

tablekeywords & ".id = pollrelate.relatesto WHERE englishkw IN (SELECT englishkw FROM " & tablekeywords & " WHERE id = " &

artid & ")"

set rs = cnarticles.execute(strSQL)

do while not rs.eof
response.write rs.fields.item("id") & " - " & rs.fields.item("pollid") & "<br>"

strSQL = "SELECT * FROM pollquestions WHERE pollid = " & rs.fields.item("pollid")

set rs2 = cnarticles.execute(strSQL)
response.write rs2.fields.item("pollques") & "<br>"
rs2.close

rs.movenext
loop
rs.close
0
Comment
Question by:hmcgeehan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
18 Comments
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 8224122
There may be syntax error for the SQL, try debug.print strSQL, copy and paste the SQL in Query to run it, see whether the generated SQL is valid or not.

regards
0
 

Author Comment

by:hmcgeehan
ID: 8224142
the SQL of this works fine

i just wanted to make it more efficient by adding a join(?) into the rs thus eliminating the need for the rs2

thanks
0
 

Expert Comment

by:pcunha
ID: 8224191
I think you problems is in the ...

WHERE englishkw IN (SELECT englishkw FROM " & tablekeywords & " WHERE id = " &

artid & ")"

If could explain the purpose os the query, I could help  you optimize.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8224284
strSQL = "SELECT DISTINCT " & tablekeywords & ".id,pollrelate.pollid,pollquestions.* FROM " & tablekeywords & " INNER JOIN pollrelate ON " &
tablekeywords & ".id = pollrelate.relatesto INNER JOIN pollquestions ON pollrelate.pollid = pollquestions.pollid WHERE englishkw IN (SELECT englishkw FROM " & tablekeywords & " WHERE id = " &
artid & ")"
0
 

Author Comment

by:hmcgeehan
ID: 8224299
shanesuebsahakarn


strSQL = "SELECT DISTINCT " & tablekeywords & ".id,pollrelate.pollid,pollquestions.* FROM " & tablekeywords & " INNER JOIN

pollrelate ON " & tablekeywords & ".id = pollrelate.relatesto INNER JOIN pollquestions ON pollrelate.pollid =

pollquestions.pollid WHERE englishkw IN (SELECT englishkw FROM " & tablekeywords & " WHERE id = " & artid & ")"


set rs = cnarticles.execute(strSQL)

do while not rs.eof
response.write rs.fields.item("id") & " - " & rs.fields.item("pollques") & "<br>"

rs.movenext
loop
rs.close


gives

Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression 'germankeywords.id = pollrelate.relatesto INNER JOIN pollquestions ON pollrelate.pollid = pollquestions.pollid'.

/../asa/related-polls.asp, line 11


thanks

0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 800 total points
ID: 8224360
Hmm, does this work ?
strSQL = "SELECT DISTINCT " & tablekeywords & ".id,pollrelate.pollid,pollquestions.* FROM (" & tablekeywords & " INNER JOIN

pollrelate ON " & tablekeywords & ".id = pollrelate.relatesto) INNER JOIN pollquestions ON pollrelate.pollid =

pollquestions.pollid WHERE englishkw IN (SELECT englishkw FROM " & tablekeywords & " WHERE id = " & artid & ")"
0
 

Author Comment

by:hmcgeehan
ID: 8224415
perfect!

im going to award the points now but i needed a slight modification to it (ill increase the points by 75 hoping youll get it!) =)


question

can you order the last recordest to show the most relevant poll first?

ie the most relevant poll is the one that shares the most similar keywords

(hope ive explained it well enough)

thanks again!
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8224519
I *think* you could do:
strSQL = "SELECT " & tablekeywords & ".id,pollrelate.pollid,pollquestions.pollques FROM (" & tablekeywords & " INNER JOIN

pollrelate ON " & tablekeywords & ".id = pollrelate.relatesto) INNER JOIN pollquestions ON pollrelate.pollid =

pollquestions.pollid GROUP BY " & tablekeywords & ".id,pollrelate.pollid,pollquestions.pollques & " WHERE englishkw IN (SELECT englishkw FROM " & tablekeywords & " WHERE id = " & artid & ") ORDER BY Count(tablekeywords.id)"

I've not tested this though.
0
 

Author Comment

by:hmcgeehan
ID: 8224668
hi

in this

Hmm, does this work ?
strSQL = "SELECT DISTINCT " & tablekeywords & ".id,pollrelate.pollid,pollquestions.* FROM (" & tablekeywords & " INNER JOIN

pollrelate ON " & tablekeywords & ".id = pollrelate.relatesto) INNER JOIN pollquestions ON pollrelate.pollid =

pollquestions.pollid WHERE englishkw IN (SELECT englishkw FROM " & tablekeywords & " WHERE id = " & artid & ")"


when i try to print pollid

ir

rs.fields.item("pollid")

it gives me an error

thanks for all the help
0
 

Author Comment

by:hmcgeehan
ID: 8224688
Expected end of statement

/../asa/related-polls.asp, line 37

strSQL = "SELECT " & tablekeywords & ".id,pollrelate.pollid,pollquestions.pollques FROM (" & tablekeywords & " INNER JOIN pollrelate ON " & tablekeywords & ".id = pollrelate.relatesto) INNER JOIN pollquestions ON pollrelate.pollid = pollquestions.pollid GROUP BY " & tablekeywords & ".id,pollrelate.pollid,pollquestions.pollques & " WHERE englishkw IN (SELECT englishkw FROM " & tablekeywords & " WHERE id = " & artid & ") ORDER BY Count(tablekeywords.id)"


pointing to the W in WHERE here
pollquestions.pollques & " WHERE englishkw IN (SELECT englishkw

thanks ill post some more points if u can sort this out
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8224722
Sorry, spurious & " in there:
strSQL = "SELECT " & tablekeywords & ".id,pollrelate.pollid,pollquestions.pollques FROM (" & tablekeywords & " INNER JOIN pollrelate ON " & tablekeywords & ".id = pollrelate.relatesto) INNER JOIN pollquestions ON pollrelate.pollid = pollquestions.pollid GROUP BY " & tablekeywords & ".id,pollrelate.pollid,pollquestions.pollques WHERE englishkw IN (SELECT englishkw FROM " & tablekeywords & " WHERE id = " & artid & ") ORDER BY Count(tablekeywords.id)"
0
 

Author Comment

by:hmcgeehan
ID: 8224861
Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression 'pollquestions.pollques WHERE englishkw IN (SELECT englishkw FROM germankeywords WHERE id = 1276)'.

/../asa/related-polls.asp, line 10
 
 


my code now



strSQL = "SELECT " & tablekeywords & ".id,pollrelate.pollid,pollquestions.pollques FROM (" & tablekeywords & " INNER JOIN

pollrelate ON " & tablekeywords & ".id = pollrelate.relatesto) INNER JOIN pollquestions ON pollrelate.pollid =

pollquestions.pollid GROUP BY " & tablekeywords & ".id,pollrelate.pollid,pollquestions.pollques WHERE englishkw IN (SELECT

englishkw FROM " & tablekeywords & " WHERE id = " & artid & ") ORDER BY Count(tablekeywords.id)"

set rs = cnarticles.execute(strSQL)

do while not rs.eof
'response.write "!" & rs.fields.item("id") & " - " & rs.fields.item("pollid") & " - " & rs.fields.item("pollques") & "<br>"



also still cant seem to write 'pollid'

thanks
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8224901
Sorry, got the GROUP BY and WHERE the wrong way round. Try:
strSQL = "SELECT " & tablekeywords & ".id,pollrelate.pollid,pollquestions.pollques FROM (" & tablekeywords & " INNER JOIN pollrelate ON " & tablekeywords & ".id = pollrelate.relatesto) INNER JOIN pollquestions ON pollrelate.pollid = pollquestions.pollid WHERE englishkw IN (SELECT englishkw FROM " & tablekeywords & " WHERE id = " & artid & ") GROUP BY " & tablekeywords & ".id, pollrelate.pollid, pollquestions.pollques ORDER BY Count(tablekeywords.id)"
0
 

Author Comment

by:hmcgeehan
ID: 8224955
strSQL = "SELECT " & tablekeywords & ".id,pollrelate.pollid,pollquestions.pollques FROM (" & tablekeywords & " INNER JOIN

pollrelate ON " & tablekeywords & ".id = pollrelate.relatesto) INNER JOIN pollquestions ON pollrelate.pollid =

pollquestions.pollid WHERE englishkw IN (SELECT englishkw FROM " & tablekeywords & " WHERE id = " & artid & ") GROUP BY " &

tablekeywords & ".id, pollrelate.pollid, pollquestions.pollques ORDER BY Count(tablekeywords.id)"
'11
response.write strSQL & "<br>"
set rs = cnarticles.execute(strSQL)

do while not rs.eof
response.write "!" & rs.fields.item("id") & " - " & rs.fields.item("pollid") & " - " & rs.fields.item("pollques") & "<br>"





gives




SELECT germankeywords.id,pollrelate.pollid,pollquestions.pollques FROM (germankeywords INNER JOIN pollrelate ON germankeywords.id = pollrelate.relatesto) INNER JOIN pollquestions ON pollrelate.pollid = pollquestions.pollid WHERE englishkw IN (SELECT englishkw FROM germankeywords WHERE id = 1276) GROUP BY germankeywords.id, pollrelate.pollid, pollquestions.pollques ORDER BY Count(tablekeywords.id)

Microsoft JET Database Engine error '80040e10'

No value given for one or more required parameters.

/../asa/related-polls.asp, line 13
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8225016
Change:
Count(tablekeywords.id)

to:
Count(germankeywords.id)
0
 

Author Comment

by:hmcgeehan
ID: 8225057
perfect!

one last thing

could this value Count(germankeywords.id) be printed? (along with pollid and pollques)

thanks again
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8225099
Sure, just add it in to the SELECT part:
strSQL = "SELECT " & tablekeywords & ".id,pollrelate.pollid,pollquestions.pollques,Count(germankeywords.id) As CountOfMatches FROM (" & tablekeywords & " INNER JOIN pollrelate ON " & tablekeywords & ".id = pollrelate.relatesto) INNER JOIN pollquestions ON pollrelate.pollid = pollquestions.pollid WHERE englishkw IN (SELECT englishkw FROM " & tablekeywords & " WHERE id = " & artid & ") GROUP BY " & tablekeywords & ".id, pollrelate.pollid, pollquestions.pollques ORDER BY Count(germankeywords.id)"
0
 

Author Comment

by:hmcgeehan
ID: 8225146
thanks again!
will post some more points for you now
=)
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question