I have a query that collects info from 2 different tables. The second query is inside a loop wich leeds me to my question. Can anybody find a more efficient solution to this code, where the 2nd query isn't inside a loop?

Set objRst = Conn.Execute ("SELECT TOP 15 * FROM tbWeb WHERE NOT clmCategory='start' ORDER BY clmDate DESC")
Do Until objRst.EOF
intNewsID = objRst("id")
Set rsCommCount = Conn.Execute ("SELECT COUNT (*) AS NumberOfComm FROM tbComments WHERE Web_ID ="& intNewsID & " ")

Response.Write objRst("clmText")
Response.Write rsCommCount("NumberOfComm")
objRst.MoveNext
Loop

The second query counts the number of comments that were made to a specific article in the other table, tbWeb.

try this:

SELECT COUNT (*) AS NumberOfComm FROM tbComments inner join tbWeb on tbComments.Web_ID =tbWeb.id
WHERE NOT tbWeb.clmCategory='start' ORDER BY tbWeb.clmDate DESC

and check it agaisnt this query

SELECT tbComments .COUNT (*) AS NumberOfComm FROM tbComments inner join tbWeb on tbComments.Web_ID =tbWeb.id
WHERE NOT tbWeb.clmCategory='start' ORDER BY tbWeb.clmDate DESC

only differnece between these to is the select count(*) and select tbComments.count(*) - just curious if it matters

After you execute the first query, you can assign data from record set to an array, and then close your record set. Then you can use the same connection to execute your second qurey with a loop of array.

I didn't understand exactly what u meant so I asked my good friend google and I found an application called GetRow wich I guess u are referring to.

I am a complete noob in asp-programming so I have never heard of this before. It seems great and fast but I don't understand exactly how it works.

I understand how it works in executing one sql-query but not how to combine them like I have in my example above. Some clarification would be greatly appriciated.

should be LEFT OUTER instead of INNER join in case there's a tbWeb row without any matching tbComments rows, you want the count to say 0