SQL/ASP question

interesting.
I am going to look into this one, and will reply back in a little while.

Carrzkiss

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

Select allOpen in new window

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

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

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=='+&nbsp;'){
      document.getElementById('plusminus_'+ID).innerHTML='-&nbsp;';
    }
    else{
      document.getElementById('plusminus_'+ID).innerHTML='+&nbsp;';
    }
    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 
 
%>

Select allOpen in new window