Problem with looping and joins... [Archive]

View Full Version : Problem with looping and joins...

Mhtml

12-01-2002, 08:19 AM

Well, I thought I had it but obviously I don't because I have run into a problem.

Why is this repeating each post for each person in the users database instead of adding the appropriate user name?

GetReplies = "SELECT Replies.*, Users.* FROM Replies, Users WHERE ToThread = " & Request.QueryString("ThreadId")
rs.Open GetReplies, conn

Do While not rs.eof
Icon = ""
%> <tr>
<td background="skins/ICE/bgtopic03.gif" width="26" height="25" align="center" valign="middle" class="BorderLeftWhite , BorderBottomWhite"><%=Icon%></td>
<td background="skins/ICE/bgtopic03.gif" width="420" class="BorderBottomWhite" valign="middle"><%=rs("UserName")%></td>
<td background="skins/ICE/bgtopic03.gif" width="218" align="right" valign="middle" class="BorderRightWhite , BorderBottomWhite">Edit
</td>
</tr>
<tr>
<td colspan="3" bgColor="e2e9ed">

<%=rs("ReplyMessage")%>

</td></tr>
<tr>
<td class="BorderLeftWhite , BorderRightWhite , BorderBottomWhite" colspan="3" bgcolor="e2e9ed" align="right"> <a class="MiniLink" href="modreport.asp?ReplyId=<%=rs("ReplyId")%>">Report this post to a moderator</a> | <a class="MiniLink" href="admodPost.asp?ReplyId=<%=rs("ReplyId")%>">Admin/Mod Options</a></td>
</tr>

<%
rs.MoveNext
loop
%>
<%
rs.close
set rs = nothing
conn.close
set conn = nothing
%>

I've attached the appropriate files including the database if you would like to see the problem for yourself.

rcreyes

12-01-2002, 04:34 PM

The first thing I noticed in your SQl statement is that you are querying two tables but you are not indicating how to link these tables together. A common field value must exist between these tables, otherwise your SQL statement will return ALL rows from both tables.
So the proper way to create a SQL query is as follows:

Select Table1.Col1, Table1.Col2, Table2.Col1 From
Table1, Table2
where
Table1.Col=Table2.Col
and
Table1.ToThread = something

Thanks,
Ray

Mhtml

12-01-2002, 11:19 PM

I had thought that somehow I needed to do a where statement for the other table but forgot about AND. lol

Thanks rcreyes! :thumbsup:

whammy

12-02-2002, 02:10 AM

I usually use a joins - here's a simple example I use on my website - note how I give the tables "aliases", which makes them easier to type out:

GETCATEGORYQUERY = "SELECT c.name AS catname, s.subname AS subcatname FROM category c INNER JOIN subcategory s ON(s.cid = c.cid) WHERE s.subid = " & subid
Set rs = Conn.Execute(GETCATEGORYQUERY)

Mhtml

12-02-2002, 02:20 AM

I just read about that in my new SQL book. I also got ASP and PHP books.

I decided it couldn't hurt to revise ASP through a book since I haven't even learnt 1 bit from a book.

whammy

12-02-2002, 02:59 AM

I learned the alias thing from a colleague who no longer works for the company I do... he's a good guy.

I actually started by reading a book... it's really the way to go... the first time you read through it you will learn a lot of stuff that you didn't know... and other stuff won't click. But after awhile, you will reread it and go "duh, too bad I didn't know what they meant there!".

I'm going through that now with the .NET book... sigh

It's OK, but I don't think I'm going to recommend this book to anyone, since it doesn't explain how to override the default behavior of .net server controls (i.e. if you want to change the HTML output to make it XHTML compatible)... funny how the whole .net platform is XML based but they either didn't allow for changes in the standards, or whoever wrote this book didn't tell you how to change the output... :mad:

Mhtml

12-02-2002, 03:04 AM

Yeah, I think I will wait a while before I learn any thing hard in .net I think I may have a few goes at using the web.config file but that's about it.

whammy

12-02-2002, 03:06 AM

Heck, I figure I might as well get started now... I'm a newbie but the sooner I start, the less time until I'm not a newbie. At least I am able to use SMTP email and make forms that work (that say "Hello, whatever... lol")... even if they are not XHTML compatible. Now I have to figure out the harder stuff. :D