#1
  1. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2014
    Posts
    69
    Rep Power
    5

    Pull in values from one query to the next


    Hi guys

    I have two sql queries on my page which queries two separate car tables. As an example, the first query simply queries table1 and returns any car models that are manufactured by Aston Martin.

    -------------------
    Code:
    <%
    sql = "Select model from table1 where manufacturer = 'aston martin'"
    
    oRs.Open sql, oDBConn 
    If oRs.eof then
    
      Else
    
            Do while not oRs.eof
    
    manufacturer = oRs("manufacturer")
    
                oRs.MoveNext
            loop 
    
      End If
    %>
    -------------------

    So, I would get results like:

    DB9
    DB7
    Vantage
    Vanquish
    DB5

    so far so good....

    The second query searches table2 for any records which match a set of car models, it would then display the top speed for each of those models. So...

    -------------------
    Code:
    <%
    sql = "Select model, topspeed from table2 WHERE model IN ('###', '###', '###')"
    
    oRs.Open sql, oDBConn 
    If oRs.eof then
    
      Else
    
            Do while not oRs.eof
    %>
    <%=oRs("model")%> = <%=oRs("topspeed")%>
    <%
    
                oRs.MoveNext
            loop 
    
      End If
    %>
    -------------------


    Here's where I have my problem. I need to pull in the models from the first query into the "IN" statement in the second query. Thus it is executed as follows:


    -------------------
    sql = "Select model, topspeed from table2 WHERE model IN ('DB9', 'DB7', 'Vantage', 'Vanquish', 'DB5')"
    -------------------

    There is no set number of models. So, For a Porsche query, I may only have 3 models but for Audi I may have 13 models that need to be pulled into the second query.

    I am an utter complete novice and have been looking at how I can do this for the past 3 days! The closest I have come is something called an array but this is way above my head. Is there an "easy" solution to this? Any help would be really appreciated.

    I look forward to hearing from you

    Best regards

    Rod from the UK
    Last edited by Will-O-The-Wisp; August 26th, 2015 at 01:32 PM.
  2. #2
  3. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2015
    Posts
    15
    Rep Power
    0
    Hello!

    Would this be better placed in one of these forums:
    Microsoft SQL Server
    SQL Development

    Or is it best suited to our ASP Development section? I can move it if it's more specific to SQL.
  4. #3
  5. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2014
    Posts
    69
    Rep Power
    5
    Hi Mark

    Thanks for your response.

    I'm not really sure really. My page is scripted entirely in asp so I'm not sure if the solution is an asp or sql one. For example, if using arrays is the solution then would it be better placed in asp? I'm just not sure.

    Regards

    Rod from the UK
  6. #4
  7. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2015
    Posts
    15
    Rep Power
    0
    Ok! We can leave it here for now then, and if someone gives more tips or clues that lean it one way or the other, I can move it then to make sure it gets in front of the right eyes.

    Thanks!
  8. #5
  9. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,665
    Rep Power
    284
    OK this is your code
    Code:
    <%
    sql = "Select model from table1 where manufacturer = 'aston martin'"
    oRs.Open sql, oDBConn 
    If oRs.eof then
      Else
            Do while not oRs.eof
                 manufacturer = oRs("manufacturer")
                oRs.MoveNext
            loop 
      End If
    %>
    you need to build second select statement dynamically
    In your code
    Code:
    <%
    sql = "Select model from table1 where manufacturer = 'aston martin'"
    oRs.Open sql, oDBConn 
    If oRs.eof then
      Else
          ' define new sql statement
           sql_dtl = "Select model, topspeed from table2 WHERE model IN (
            Do while not oRs.eof
                manufacturer = oRs("manufacturer")
                 sql_dtl =  sql_dtl & " '" & oRs("manufacturer") & "',"
                oRs.MoveNext
            loop 
            ' now remove last comma and add close ") "      
            sql_dtl = left(sql_dtl, len(sql_dtl)-1) & ")"
      End If
    ' To get second part just use from your code
    
    oRs.Open sql_dtl , oDBConn 
    If oRs.eof then
      Else
            Do while not oRs.eof
    %>
    <%=oRs("model")%> = <%=oRs("topspeed")%>
    <%
                oRs.MoveNext
            loop 
      End If
    %>
    Last edited by Will-O-The-Wisp; August 31st, 2015 at 10:00 AM.
    GK
    __________________________________________________ _____
    if you found this post is useful click Give Rep button (bottom side on this reply ) and agree
  10. #6
  11. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2014
    Posts
    69
    Rep Power
    5
    Excellent - thanks GK!

    Best regards

    Rod from the UK

Similar Threads

  1. MultiColumn Combo Values pull wrong column
    By Mattwesterville in forum Microsoft Access Help
    Replies: 1
    Last Post: June 22nd, 2008, 12:42 PM
  2. Query to Pull First & Last Record?
    By xkasy in forum Microsoft Access Help
    Replies: 2
    Last Post: January 4th, 2007, 10:11 AM
  3. Pull Down Query List
    By Solitaire in forum Microsoft Access Help
    Replies: 2
    Last Post: November 2nd, 2006, 11:08 AM
  4. Anyway for a css file to pull values out of a mysql database?
    By Pemolis in forum HTML, JavaScript And CSS Help
    Replies: 1
    Last Post: July 12th, 2006, 05:14 PM
  5. Replies: 2
    Last Post: June 16th, 2001, 05:18 PM

IMN logo majestic logo threadwatch logo seochat tools logo