I think at that point testing to see which method is going to
be the most efficient on the server/database to the script portions
if the table struture looks something like thi
tblFamily
Family_Ident
Family_Name
Family_Address
Family_Phone
Family_Email
tblPicture
Picture_Ident
Family_Key
Family_Picture
and your primary to foriegn is the Family_Ident -> Family_Key
the best way to group a query on the 2 tables is what you stated as
Code:
SELECT
tblFamily.Family_Name,
tblFamily.Family_Address,
tblFamily.Family_Phone,
tblFamily.Family_Email,
tblPicture.Family_Picture
FROM tblFamily
INNER JOIN tblPicture ON tblFamily.Ident = tblPicture.Family_Key
ORDER BY tblFamily.Family_Name;
that would return something as
Code:
Family_Name Family_Address Family_Phone Family_Email Family_Picture
myName 9999 999-999-9999 email@email.com /path/pic.gif
myName 9999 999-999-9999 email@email.com /path/pic2.gif
myName 9999 999-999-9999 email@email.com /path/pic3.gif
myName 1111 111-111-1111 email@email.com /path/pic.gif
myName 1111 111-111-1111 email@email.com /path/pic2.gif
So the first test would be to grab that resulting data and see how efficient it is
to get what you need out of it. logic form I would think up something like
this for a JOIN on the tables and run through while having a nested loop to grab
all the relational pictures and print them while only printing the primary data once.
Code:
SQL = "SELECT " & _
"tblFamily.Ident, " & _
"tblFamily.Family_Name, " & _
"tblFamily.Family_Address, " & _
"tblFamily.Family_Phone, " & _
"tblFamily.Family_Email, " & _
"tblPicture.Family_Key, " & _
"tblPicture.Family_Picture " & _
"FROM tblFamily " & _
"INNER JOIN tblPicture ON tblFamily.Ident = tblPicture.Family_Key " & _
"ORDER BY tblFamily.Ident;"
RS.Open(SQL),conn,3,3
Dim IdentHold
Dim IdentCompare
If NOT RS.EOF Then
IdentHold = RS("Ident")
IdentCompare = RS("Family_Key")
End If
Do While NOT RS.EOF
IdentCompare = RS("Family_Key")
IdentHold = RS("Ident")
Response.Write RS("Family_Name") & "<br>"
Do While NOT RS.EOF AND cInt(IdentHold) = cInt(IdentCompare)
Response.Write RS("Family_Picture") & "<br>"
RS.MoveNext
If NOT RS.EOF Then
IdentCompare = RS("Family_Key")
End If
Loop
Loop
In your case I would tend to say (and what I would go with) is the two recordset's.
That due to maintainability in all. I curious to test the efficiency of both though.
the things you would want to take into careful consideration here are
1) the time to hit the database with the type of query (SQL Statement)
2) the looping structure time
3) the needs for multiple defined variables etc. for teh actual looping structures
4) the time needs for opening the multiple to single object (recordset)
Let's get a bit donw to the real world though for a minute also.
In a application that is this small in data read, there is going to be little
difference in the methods. Now if you get to a larger data source, say 100,000+
then I would go with the JOIN and looping structure above more so in my initial tests.
The reasoning for that is mainly due to your statements here
lovejaeeun said:
This seems like a lot of iterations because at least 95% of the families have pictures.
So the first loop will go through 1,200 loops and for each of those loops it will
do another 1,200 * .95 loops. There has to be a better way?
In the structure above that is not going to happen. What will happen is it will in reality
loop through one recordset only one time. The conditioning jsut gets a bit more indepth to
catch where and where not to perform the tasks needed.
___________________________________________________________________
The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page faq333-3811
![[lol]](/data/assets/smilies/lol.gif "[lol] [lol]")