Need advice on getting data out of normalized database

[Dragonhunter]

Hello all,

Thanks to the advice of Manohar Kamath, Bob Barrows, Curt, Chris and
others, I restructured my database for "normalization" where I have a
main table with a couple columns and then in the columns I've got ID
numbers of items in other tables. The idea makes sense. However, when
I go to get data out of my database, it seems tedious. I'm wondering if
I am doing it right. Is this how you guys would do it? I have a loop
with a bunch of .execute statements in each (see code below) Any tips
on how to improve it would be greatly appreciated!!

-Dragonhunter

Here's the table structures:

---Magic_Items_Table---
....other fields....
Rarity (ID of item from Rarity table)
Aura_Strength (ID of item from Aura_Strength table)
Aura_Type (ID of item from Aura_Type table)

---Rarity Table---
ID
Rarity_Options

---Aura_Strength Table---
ID
Aura_Strength

---Aura_Type-----
ID
Aura_Type


Dim oConn, sConnString
Set oConn = Server.CreateObject("ADODB.Connection")
sConnString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=" & Server.MapPath(database_relative_path) & ";"
oConn.Open(sConnString)
set rs = oConn.Execute("SELECT * FROM Magic_Items_Table")
%>
<table border="0" width="252" cellspacing="0" cellpadding="0">
<%while not rs.EOF%>
<%if (not isNull(rs.Fields("Description").Value)) then%>
<tr>
<td height="429" valign="top" >
<table border="0" width="100%" cellspacing="0" cellpadding="0">
<tr>
<br/><strong>Rarity</strong>: <%
if (not (isNull(rs.fields("Rarity").Value) or
rs.fields("Rarity").Value = "")) then
'Response.Write(">"&rs.fields("Rarity").Value&"<")
set rs2 = oConn.Execute("SELECT Rarity_Options FROM Rarity WHERE
ID="&rs.fields("Rarity").Value)
If rs2.eof then
Response.Write("EOF")
Else
Response.Write(rs2.Fields(0).Value)
End if
end if
%>

<br/><strong>Aura</strong>: <%
if (not (isNull(rs.fields("Aura_Strength").Value) or
rs.fields("Aura_Strength").Value = "")) then
'Response.Write(">"&rs.fields("Rarity").Value&"<")
set rs3 = oConn.Execute("SELECT Aura_Strength FROM Aura_Strength
WHERE ID="&rs.fields("Aura_Strength").Value)
If rs3.eof then
Response.Write("EOF")
Else
Response.Write(rs3.Fields(0).Value)
End if
end if
Response.Write(" ")
if (not (isNull(rs.fields("Aura_Type").Value) or
rs.fields("Aura_Type").Value = "")) then
'Response.Write(">"&rs.fields("Rarity").Value&"<")
set rs3 = oConn.Execute("SELECT Aura_Type FROM Aura_Type WHERE
ID="&rs.fields("Aura_Type").Value)
If rs3.eof then
Response.Write("EOF")
Else
Response.Write(lcase(rs3.Fields(0).Value))
End if
end if

%>

</td>
</tr>
</table>
</td>
</tr>
<%end if%>
<%
rs.movenext
wend
%>
</table>

[Aaron Bertrand - MVP]

> set rs2 = oConn.Execute("SELECT Rarity_Options FROM Rarity WHERE

> ID="&rs.fields("Rarity").Value)

Ugh, no, you should not be nesting recordsets. Use joins and make your life
easier.
[url]http://www.aspfaq.com/2241[/url]

[Jeff Cochran]

On Tue, 23 Sep 2003 18:10:06 GMT, Dragonhunter
<dragonhunter97@yahoo.com> wrote:

>Thanks to the advice of Manohar Kamath, Bob Barrows, Curt, Chris and
>others, I restructured my database for "normalization" where I have a
>main table with a couple columns and then in the columns I've got ID
>numbers of items in other tables. The idea makes sense. However, when
>I go to get data out of my database, it seems tedious. I'm wondering if
>I am doing it right. Is this how you guys would do it? I have a loop
>with a bunch of .execute statements in each (see code below) Any tips
>on how to improve it would be greatly appreciated!!

Normally (pun intended) when you normalize to separate tables, you use
JOIN statements to query the data you need. You may want to run
through the tutorials at sqlcourse.com for a primer/review of SQL
statements.

Your code nests recordsets, which is messy and problematic, and you
can handle the query in a simple JOIN on the relevant tables and
columns in a single recordset and a single trip to the database.

Jeff