JOIN tables

[fsqueeen]

hie hi...it's me again..i almost finish my system..=)

juz a minor problem..i guess..

is about the SQL to join tables, let explain my situation 1st..

i hv 3 tables here, LOAN, TRANSACTION, PRODUCT

TRANSACTION is the composite entity which relates LOAN and PRODUCT.

now i wan to get data from LOAN and PRODUCT.

my code:

Set rs_1 = Server.CreateObject("ADODB.Recordset&quot
SQL_1="SELECT company.company_name , loan.loan_id AS loan_id, loan.loan_date, loan.due_date FROM company, loan WHERE company.company_id=loan.company_id AND loan_id = '"&LoanID&"' "
Set rs_1 = MyDB.execute(ReturnSQL_1)

LID=rsReturn_1("loan_id&quot

Set rsReturn = Server.CreateObject("ADODB.Recordset&quot
ReturnSQL="SELECT product.product_name FROM product, transaction WHERE product.product_id=transaction.product_id AND loan_id ="& LID & ""
Set rsReturn = MyDB.execute(ReturnSQL)
%>

<form >

<TABLE >
<TR >
</TR>

<% While Not rs_1.EOF %>
<TR>
<TD width=&quot;150&quot;><font face=&quot;Century Gothic&quot; size=&quot;3&quot;>
<% Response.Write rs_1(&quot;loan_id&quot %>
</font></TD>

<TD><font face=&quot;Century Gothic&quot; size=&quot;3&quot;>
<% Response.Write rs_1(&quot;company_name&quot %>
</font></TD>
<TD><font face=&quot;Century Gothic&quot; size=&quot;3&quot;>
<% Response.Write rs_1(&quot;loan_date&quot %>
</font></TD>
<TD><font face=&quot;Century Gothic&quot; size=&quot;3&quot;>
<% Response.Write r_1(&quot;due_date&quot %>
</font></TD>

***** here's my problems*****

<%If Not rsReturn.EOF Then%>
<%While Not rsReturn.EOF %>
<TD>
<font face=&quot;Century Gothic&quot; size=&quot;3&quot;>
Response.Write rsReturn(&quot;product_name&quot %>
</font>


</TD> <% rsReturn.MoveNext
'Wend
%>

<%Else%>

<TD><font face=&quot;Century Gothic&quot; size=&quot;3&quot;><b><font color=&quot;#FF0000&quot;>No Item</font></b>
</font></TD>

<%'nd If%>

</TR>
<%
rsReturn_1.MoveNext
Wend
rsReturn.Close
rsReturn_1.Close

MyDB.Close

Set rsReturn = Nothing
Set rsReturn_1 = Nothing
Set MyDB = Nothing
%>
<TR>
<TD colspan=&quot;4&quot; width=&quot;150&quot;> <br>
<table width=&quot;300 &quot;>
<tr>
<td><font size=&quot;3&quot;><b><font face=&quot;Century Gothic&quot; color=&quot;#FF0000&quot;>Please
enter the Loan Form No.</font></b></font>
<br><br>
<input type=&quot;text&quot; name=&quot;LoanID&quot;>
<input type=&quot;submit&quot; name=&quot;Submit&quot; value=&quot;GO!!&quot;>
</td></tr>
</table>
</TD></TR>
</Table>
</form>


***** Questions ********
we can't have 2 &quot;while&quot; function in the codes right..but i hv 2 recordsets...how can i solve this situation?..i need to loop both the recordset...

How?

 

[JuanitaC]

Maybe I am missing something, but I think you are referencing a recordset that doesn't exist... Where you build your SQL statements, you have two recordsets: rs_1 and rsReturn, but throughout your code, you reference rsReturn and rsReturn_1.

Where is rsReturn_1 coming from?

 

[fsqueeen]

Ooops...sorry
i changed rsReturn_1 --> rs_1
and rsReturn -- >rs

to make the codes look easy to u all...sorry