DesiMck
asked on
SQL/ASP question
Hi,
I have attached my db to simplify things. I want to display the answers and comments in the following way:
Question 1
-------------Answer 1
-------------------------c omment a
-------------------------c omment b
-------------Answer 2
-------------Answer 3
-------------------------c omment c
etc
So for each question the answers will be listed and any comment associated with the answers will be display under each answer. Some answers may noy have comments.
What would be the correct SQL and asp code to display this. I am using Access.
Hope this makes sense.
Facilitate1.mdb
I have attached my db to simplify things. I want to display the answers and comments in the following way:
Question 1
-------------Answer 1
-------------------------c
-------------------------c
-------------Answer 2
-------------Answer 3
-------------------------c
etc
So for each question the answers will be listed and any comment associated with the answers will be display under each answer. Some answers may noy have comments.
What would be the correct SQL and asp code to display this. I am using Access.
Hope this makes sense.
Facilitate1.mdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Taking carrzkiss' excellent answer and offering variation would be to use a different SQL that could be employed would be as per below. Thus the class for the <td> can then be based off the returned "indent" column (also below). This will return all questions, answers etc
SELECT indent, textcol
FROM (SELECT "Q" AS indent,
tblquestions.questionid,
0 AS answerid,
0 AS commentid,
tblquestions.question AS textcol
FROM tblquestions
UNION ALL
SELECT "A" AS indent,
questionid,
answerid,
0 AS commentid,
answer
FROM tblanswer
UNION ALL
SELECT "C" AS indent,
tblanswer.questionid,
tblanswer.answerid,
tblcomment.commentid,
tblcomment.COMMENT
FROM tblanswer INNER JOIN tblcomment
ON tblanswer.answerid = tblcomment.answerid)
ORDER BY questionid, answerid, commentid;
' ASP loop logic
Response.Write "<table>"
do while not rs.EOF
' This is for your Questions
if rs("indent") = "Q" then
Response.Write "<tr><td class=""parent"" >"&rs("textcol")&"</td></tr>" & vbCrLf
elsif rs("indent") = "A" then
Response.Write "<tr><td class=""child"">" & rs("textcol") & "</td></tr>" & vbCrLf
else
Response.Write "<tr><td class=""com"">" & rs("textcol") & "</td></tr>" & vbCrLf
end if
rs.MoveNext
i = i + 1
loop
Response.Write "</table>"
rs.close
ASKER
Hi,
thank you both. I got carrzkiss's solution to work but it did not quite work out the way I want. This was the output.
Test question 1?
_____Answer to Q1
__________comment to Q1A1
_____Answer to Q1
__________comm to Q1A1 part 2
_____Another answer to Q1
__________com to A3 Q1
However this is what I need (its similar but the comments are under the answer with only appears once)
Test question 1?
_____Answer to Q1
__________comment to Q1A1
__________comm to Q1A1 part 2
_____Another answer to Q1
__________com to A3 Q1
I tried the second solution but get the following error:
Microsoft JET Database Engine error '80040e10'
No value given for one or more required parameters.
/facilitate/list1.asp, line 63
This is the SQL - I have to remove the double quotes arounf the Q, A and C:
SELECT indent, textcol FROM (SELECT Q AS indent, tblquestions.questionid, 0 AS answerid, 0 AS commentid, tblquestions.question AS textcol FROM tblquestions UNION ALL SELECT A AS indent, questionid, answerid, 0 AS commentid, answer FROM tblanswer UNION ALL SELECT C AS indent, tblanswer.questionid, tblanswer.answerid, tblcomment.commentid, tblcomment.COMMENT FROM tblanswer INNER JOIN tblcomment ON tblanswer.answerid = tblcomment.answerid) ORDER BY questionid, answerid, commentid
Many thanks,
Desi
thank you both. I got carrzkiss's solution to work but it did not quite work out the way I want. This was the output.
Test question 1?
_____Answer to Q1
__________comment to Q1A1
_____Answer to Q1
__________comm to Q1A1 part 2
_____Another answer to Q1
__________com to A3 Q1
However this is what I need (its similar but the comments are under the answer with only appears once)
Test question 1?
_____Answer to Q1
__________comment to Q1A1
__________comm to Q1A1 part 2
_____Another answer to Q1
__________com to A3 Q1
I tried the second solution but get the following error:
Microsoft JET Database Engine error '80040e10'
No value given for one or more required parameters.
/facilitate/list1.asp, line 63
This is the SQL - I have to remove the double quotes arounf the Q, A and C:
SELECT indent, textcol FROM (SELECT Q AS indent, tblquestions.questionid, 0 AS answerid, 0 AS commentid, tblquestions.question AS textcol FROM tblquestions UNION ALL SELECT A AS indent, questionid, answerid, 0 AS commentid, answer FROM tblanswer UNION ALL SELECT C AS indent, tblanswer.questionid, tblanswer.answerid, tblcomment.commentid, tblcomment.COMMENT FROM tblanswer INNER JOIN tblcomment ON tblanswer.answerid = tblcomment.answerid) ORDER BY questionid, answerid, commentid
Many thanks,
Desi
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Many thanks for your help - great solutions.
DesiMck:
Glad that we could help.
Would you mind provided your working code. Or did you go another route in this?
Carrzkiss
Glad that we could help.
Would you mind provided your working code. Or did you go another route in this?
Carrzkiss
ASKER
Of couse - here is the code - It's a mesh of both answers:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%' Please read all the little notes in this script to make sure that all works as designed%>
<style>
.parent{
font-weight: bold;
font-family: Arial;
font-size: 14px;
cursor:hand;
}
.child{
font-family: arial;
font-size:11px;
padding-left: 20px;
}
.Com{ /*This is for the Comments, change to suite...*/
font-family:arial;
font-size:11px;
padding-left:20px;
text-indent:20px;/*Make sure that you keep this in, You can change the px size to what ever you desire, BUT do not remove it. */
}
</style>
<script type="text/javascript">
function showOrHide(ID){
if (document.getElementById('plusminus_'+ID).innerHTML=='+ '){
document.getElementById('plusminus_'+ID).innerHTML='- ';
}
else{
document.getElementById('plusminus_'+ID).innerHTML='+ ';
}
if (document.getElementById('td_'+ID).style.display=='none'){
document.getElementById('td_'+ID).style.display='block';
}
else{
document.getElementById('td_'+ID).style.display='none';
}
}
</script>
<%
Dim conn
Dim rs
Dim sSQL
Dim sLastParentCat
Dim sCat
Dim i
sLastParentCat = ""
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("Facilitate.mdb")
set conn = server.createobject("adodb.connection")
conn.open strconn
i = 0
set rs = server.CreateObject("adodb.recordset")
QID = Request("QID") ' This will get the id=1 from your QueryString
sSQL="SELECT indent, textcol FROM (SELECT 1 AS indent, tblquestions.questionid, 0 AS answerid, 0 AS commentid, tblquestions.question AS textcol "
sSQL=sSQL&"FROM tblquestions UNION ALL SELECT 2 AS indent, questionid, answerid, 0 AS commentid, answer FROM tblanswer UNION ALL SELECT 3 AS indent, "
sSQL=sSQL&"tblanswer.questionid, tblanswer.answerid, tblcomment.commentid, tblcomment.COMMENT FROM tblanswer INNER JOIN tblcomment ON tblanswer.answerid = tblcomment.answerid) "
sSQL=sSQL&"WHERE (((tblQuestions.QuestionID)="&Request("QID")&")) ORDER BY questionid, answerid, commentid"
rs.Open sSQL, conn, 1, 3, 1
' ASP loop logic
Response.Write "<table width = '100%'>"
do while not rs.EOF
' This is for your Questions
if rs("indent") = 1 then
Response.Write "<p><tr><td class=""parent"" = 1>"&rs("textcol")&"</td></tr></p>" & vbCrLf
elseif rs("indent") = 2 then
Response.Write "<tr><td class=""child"">" & rs("textcol") & "</td></tr>" & vbCrLf
else
Response.Write "<tr><td class=""com"">" & rs("textcol") & "</td></tr>" & vbCrLf
end if
rs.MoveNext
i = i + 1
loop
Response.Write "</table>"
rs.close
set rs = nothing
conn.close
set conn = nothing
%>
I am going to look into this one, and will reply back in a little while.
Carrzkiss