?
Solved

more efficient sql statement?

Posted on 2003-03-28
18
Medium Priority
?
173 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
18 Comments
 
LVL 55

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

616 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