Problem with SQL query

[Herminio]

i'm trying to put this at work:

I have DB that as some articles that users put in, those articles can be commented by other users, what i want to do is to display the article and the comments that it eventually have, the comments are in a separated table i just need to know how do i call all the articles and all of the comments for that article all in one page, i've managed to do it in separated pages just putting a link for the comments page and passing the id of the article, but i want to do it all on one page, can someone help me?

Herminio, Portugal

 

[JamesLean]

I take it you have two tables such as:

Article:
ArticleID (PK),
Details,
...

Comment:
CommentID (PK),
ArticleID (FK to Article table),
Details,
...

To get an article and all it's comments you can issue a query such as this:

SELECT *
FROM article a JOIN comment c ON a.articleid = c.articleid
WHERE a.articleid = 1234

--James

 

[Herminio]

you're wright , but the problem is that i don't give any value to the "where statement" i want to display all the articles and comments without specifying one in particular

example:

article1 title
article1 text
article1 comment

article2 title
article2 text
no comments yet for this article

article3 title
article3 text
article3 comment1
article3 comment2
article3 comment3

hope you understand what i want

 

[JamesLean]

OK, use this SQL:

SELECT *
FROM article a LEFT JOIN comment c ON a.articleid = c.articleid
ORDER BY a.articleid, c.commentid

(Use the left join to include any articles without comments)

Then on your ASP page, while looping thru the recordset and displaying the results, use a variable to compare the current article id to the last one and only print the article details if they change:

Dim lastarticle
lastarticle = ""

Do Until rs.EOF
  If rs(&quot;articleid&quot;) <> lastarticle Then
    Response.Write &quot;<b>&quot; & rs(&quot;title&quot;) & &quot;</b><br>&quot;
    Response.Write rs(&quot;article_text&quot;)
  End If

  If rs(&quot;commentid&quot;) <> &quot;&quot; Then
    Response.Write &quot;<p><i>&quot; & rs(&quot;comment_text&quot;) & &quot;</i>&quot;
  Else
    Response.Write &quot;<p>no commments&quot;
  End If

  lastarticle = rs(&quot;articleid&quot;)

  rs.MoveNext
Loop

I've not tested it and the formatting is just a suggestion but you should be able to tweak it to what you want. --James

 

[Herminio]

every thing looks fine but there's a problem

this is what i'm getting

teste --> first article
mais um teste --> comment for the first article

no commments --> no comment article 2 missing the article

no commments --> no comment article 3 missing the article

no commments --> no comment article 4 missing the article

no commments --> no comment article 5 missing the article

no commments --> no comment article 6 missing the article

can you tell me what's wrong?

 

[Herminio]

i managed to solve the problem it was just a litle miss

this is my final version

Dim lastarticle
lastarticle = ""

Do Until opin.EOF
If opin(&quot;idopiniao&quot <> lastarticle Then
Response.Write &quot;<b>&quot; & opin(&quot;titulo&quot & &quot;</b><br>&quot;
Response.Write opin(&quot;opiniao&quot
End If
If opin(&quot;idcoment&quot <> &quot;&quot; Then
Response.Write &quot;<p><i>&quot; & opin(&quot;coment&quot & &quot;</i><p>&quot;
Else
Response.Write opin(&quot;opiniao&quot
Response.Write &quot;<p>no commments<p>&quot;
End If

lastarticle = opin(&quot;idopiniao&quot

opin.MoveNext
Loop

 

[JamesLean]

Are you sure the query is returning the expected resultset? Firstly, change

SELECT *

to specify a column list:

SELECT a.articleid, a.title, a.article_text, c.commentid, c.comment_text
FROM article a LEFT JOIN comment c ON a.articleid = c.articleid
ORDER BY a.articleid, c.commentid

Then try executing that directly in your database and check what it returns. It should have at least one row for each article (including those that don't have any comments) and multiple rows for articles which have more than one comment.

I've also noticed you need to add a new paragraph when you output the article title:

[tt]...
If rs(&quot;articleid&quot <> lastarticle Then
Response.Write &quot;<p><b>&quot; & rs(&quot;title&quot & &quot;</b><br>&quot;
...[/tt] --James