Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    11

    Unanswered: sql-query question

    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.
    Last edited by da frog; 03-21-04 at 14:46.

  2. #2
    Join Date
    Jan 2003
    Location
    New York
    Posts
    160
    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
    Beyond Limitation

  3. #3
    Join Date
    Dec 2003
    Posts
    454
    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.

  4. #4
    Join Date
    Jan 2004
    Posts
    11
    Originally posted by gyuan
    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.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    PHP Code:
    select top 15 
           tbWeb
    .id
         
    tbWeb.clmDate
         
    count(tbComments.Web_ID) as NumberOfComm
      from tbWeb 
    left outer
      join tbComments  
        on tbWeb
    .id 
         
    tbComments.Web_ID
     where not clmCategory 
    'start' 
    order 
        by tbWeb
    .clmDate desc 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •