Query inside a loop

[delg]

Simple one (I hope!)
I have a table with stock and another table with pictures of the stock. I can loop through the stock table and output it in a register page but how do i populate one of the columns in my HTML table with the pictures stored in the db? The Stock table has a PK which is stored in the File (pictures) table but I cant join the two via SQL as I will get 4 Stock records if there are 4 File Rows with the connecting FK value.
Its easy to do in Cold Fusion but I cant get it to work in ASP.
Thanks for any help!

 

[LV]

So you need to display Stock once in one column and associated pictures in another, like this:

Stock1 Picture1-1
Picture1-2
Picture1-3

Stock2 Picture2-1
Picture2-2
Picture2-3

Is that right?

 

[LV]

So say you jonined the two tables and retrieved a recordset called "rsStock" (which will bring miltiple Stocks, as many associated Pictures you have in the Pictures table) and this recordset has field "StockId" in it(or whatever your PK in Stock table is).

<table>
<tr>
<td>
Stock
</td>
<td>
Pictures
</td>
</tr>
<%
CurrentStockId = &quot;-1&quot;
do while not rsStock.EOF
if rsStock(&quot;StockId&quot <> CurrentStockId then CurrentStockId = rsStock(&quot;StockId&quot
%>
<tr>
<td>
<% =rsStock(&quot;StockName&quot %>
</td>
</tr>
<% end if %>

<tr>
<td></td>
<td>
<%
'get picture using rsStock(&quot;PicturePath&quot
Response.Write rsStock(&quot;PicturePath&quot
%>
</td>
</tr>
<%
rsStock.MoveNext
loop

%>
</table>

Just realized that this technique will give you a slightly different output:

Stock1
Picture1-1
Picture1-2
Picture1-3
Picture1-4

Stock2
Picture2-1
Picture2-2
Picture2-3

Bur I'm sure it won't be a big deal for you to get it into a format you want.

 

[DreXor]

you could also do the join and avoid multiple recordsets and just say StockID comparisons to see if the last row is the same product, then you can skip the text return information and just populate another image from the row info.