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

    Join Date
    Dec 2009
    Posts
    4
    Rep Power
    0

    Query multiple tables using SQL / ASP


    HI

    I have three tables as below
    Customer Table
    client key-----Name
    1-----Jim
    2-----Tom
    3-----Will
    4-----Fred



    Stats Table
    Stats Key-----client key
    11-----1
    22-----2
    33-----3
    44-----4



    Results Table
    Results Key-----Client key-----Results
    111-----1-----700 goals
    222-----2-----900 goals
    333-----3-----400 goals
    444-----4-----700 goals



    Now , there is a relationship between results table and customer table / stats table and customer table , as I need to output the following data, and the filter is 700 goals




    for example

    Output
    Customer.client key Customer.Name Results.Results Key-----Results.client key-----Results.results-----Stats.stats key
    1-----Jim 111-----1-----700 Goals-----11
    4-----Fred-----444-----4 700 Goals-----44




    Can someone help with the SQL code that will work

    I have tried various things and cannot get to work.

    Thanks
  2. #2
  3. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jul 2009
    Posts
    14
    Rep Power
    0
    try this one man

    select Customer.client_key, Customer.Name, Results.Results_Key,Results.client_key,Results.res ults,Stats.stats_key
    from customer inner join stats on Customer.client_key=Stats.Client_key
    and inner join Results on Customer.client_key=Results.Client_key
  4. #3
  5. 0x800A0C93
    ASP Super Hero (2500 - 2999 posts)

    Join Date
    Jan 2009
    Location
    Ridin' through this world...
    Posts
    2,975
    Rep Power
    1313
    Without constructing it all and actually testing it, try something like this to start:

    Code:
    select a.client_key, a.client_name, b.results_key,
    b.client_key, b.results, c.stats_key
    from customer a left join results b on a.client_key = b.client.key left join
    stats c on a.client_key = c.client_key
    where b.results = "700 goals"
    order by a.client_key
    Brinkster- free development account. Not affiliated, but I sure like free. Brinkster.com
  6. #4
  7. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Dec 2009
    Posts
    4
    Rep Power
    0
    Originally Posted by steve Strong
    try this one man

    select Customer.client_key, Customer.Name, Results.Results_Key,Results.client_key,Results.res ults,Stats.stats_key
    from customer inner join stats on Customer.client_key=Stats.Client_key
    and inner join Results on Customer.client_key=Results.Client_key

    This is what I tried using real world case;

    query = "select CLIENTS.NAME_ADDRESS_1, CLIENTS.CLIENT_NUMBER, CLIENT_ANALYSIS_CODES.ANALYSIS$$, ACCOUNT_NUMBERS.CREDIT_LIMIT_PREC_NM"
    query = query & "FROM CLIENTS"
    query = query & "INNER JOIN CLIENT_ANALYSIS_CODES ON CLIENTS.CLIENT_NUMBER=CLIENT_ANALYSIS_CODES.CLIENT $$"
    query = query & "AND INNER JOIN ACCOUNT_NUMBERS ON CLIENTS.CLIENT_NUMBER=ACCOUNT_NUMBERS.CLIENT$$"



    receive this error;

    Microsoft OLE DB Provider for ODBC Drivers error '80004005'

    [TOD][ODBC][GENESIS](pos: 143 '...LIENTSINNER ^JOIN CLIENT_...') - syntax error

    /default2.asp, line 27


    any ideas?
  8. #5
  9. 0x800A0C93
    ASP Super Hero (2500 - 2999 posts)

    Join Date
    Jan 2009
    Location
    Ridin' through this world...
    Posts
    2,975
    Rep Power
    1313
    I don't think you want the AND in front of the second inner join. I think you need a structure like "from (a inner join b ) inner join c" if you want to go this way.
  10. #6
  11. No Profile Picture
    Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Posts
    1,895
    Rep Power
    309
    Steve has it right
    Code:
    strSQL = "SELECT customer.clientkey, "_
           & "customer.name, "_
           & "results.resultskey, "_
           & "results.clearkey, "_
           & "results.results, "_
           & "stats.statskey "_
           & "FROM customer "_
           & "INNER JOIN stats "_
           & "ON customer.clientkey = stats.clientkey "_
           & "INNER JOIN results "_
           & "ON customer.clientkey = results.clientkey "_
           & "WHERE results.results = '700 goals' "_
           & "ORDER BY customer.name;"
  12. #7
  13. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Dec 2009
    Posts
    4
    Rep Power
    0
    Thanks everyone for input, however I cannot get this to work

    I'm getting HTTP 500 Internal Server Error , when I run in browser.

    The web services are fine, as I can run othe asp code. Please forget me as this is my 1st attempt at asp / sql.

    Can someone take a look at the code and adise what is wrong.
    Thanks


    Code:
    <% Response.Buffer = True %>
    <html>
    <head>
    <title>TEST</title>
    </head>
    <body>
    <%
    
    Dim rs, strSQL
    
    
    Set conn=Server.CreateObject("ADODB.Connection")
    Set rs = Server.CreateObject("ADODB.Recordset")
    conn.Open "TEST_ODBC"
    
    
    strSQL = "SELECT CLIENTS.CLIENT_NUMBER, "_
           & "CLIENTS.NAME_ADDRESS_1, "_
           & "CLIENT_ANALYSIS_CODES.ANALYSIS$$, "_
           & "CLIENT_ANALYSIS_CODES.CLIENT$$, "_
           & "ACCOUNT_NUMBERS.CREDIT_LIMIT_PREC_NM, "_
           & "ACCOUNT_NUMBERS.CLIENT$$ "_
           & "FROM CLIENTS "_
           & "INNER JOIN CLIENT_ANALYSIS_CODES "_
           & "ON CLIENTS.CLIENT_NUMBER = CLIENT_ANALYSIS_CODES.CLIENT$$ "_
           & "INNER JOIN ACCOUNT_NUMBERS "_
           & "ON CLIENTS.CLIENT_NUMBER = ACCOUNT_NUMBERS.CLIENT$$ "_
           & "WHERE CLIENT_ANALYSIS_CODES.ANALYSIS$$ LIKE '%IMCR%' "_
           & "ORDER BY CLIENTS.NAME_ADDRESS_1;"
      
      
    Set rs = Conn.Execute(strSQL)
    
    While Not rs.EOF
    
    Response.Write "ID : " & rs("CLIENT_NUMBER") & "<br>"
    Response.Write "ID : " & rs("NAME_ADDRESS_1") & "<br>"
    Response.Write "ID : " & rs("ANALYSIS$$") & "<br>"
    Response.Write "ID : " & rs("CREDIT_LIMIT_PREC_NM") & "<br>"
    
    
    rs.MoveNext
    
    Wend
    
    	rs.Close
    	Set rs = Nothing
      Conn.Close
      Set Conn = Nothing
    
    
    
    %>
    </body>
    </html>
  14. #8
  15. 0x800A0C93
    ASP Super Hero (2500 - 2999 posts)

    Join Date
    Jan 2009
    Location
    Ridin' through this world...
    Posts
    2,975
    Rep Power
    1313
    If that is all of your code I don't think you have a sufficient connection string.

    I would have expected something like:

    Code:
    Dim rs, conn, strSQL, datasource
    
    Set conn=Server.CreateObject("ADODB.Connection")
    
    datasource = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &_
    Server.MapPath("name of database")
    
    conn.Mode = 3
    conn.Open datasource
    
    strSQL = "SELECT CLIENTS.CLIENT_NUMBER, "_
           & "CLIENTS.NAME_ADDRESS_1, "_
           & "CLIENT_ANALYSIS_CODES.ANALYSIS$$, "_
           & "CLIENT_ANALYSIS_CODES.CLIENT$$, "_
           & "ACCOUNT_NUMBERS.CREDIT_LIMIT_PREC_NM, "_
           & "ACCOUNT_NUMBERS.CLIENT$$ "_
           & "FROM CLIENTS "_
           & "INNER JOIN CLIENT_ANALYSIS_CODES "_
           & "ON CLIENTS.CLIENT_NUMBER = CLIENT_ANALYSIS_CODES.CLIENT$$ "_
           & "INNER JOIN ACCOUNT_NUMBERS "_
           & "ON CLIENTS.CLIENT_NUMBER = ACCOUNT_NUMBERS.CLIENT$$ "_
           & "WHERE CLIENT_ANALYSIS_CODES.ANALYSIS$$ LIKE '%IMCR%' "_
           & "ORDER BY CLIENTS.NAME_ADDRESS_1;"
      
    Set rs = conn.execute(strSQL)
    but perhaps you have your connection information elsewhere.

Similar Threads

  1. query AND/OR for textboxes
    By gilgalbiblewhee in forum ASP Development
    Replies: 1
    Last Post: February 4th, 2005, 07:39 PM
  2. "where" clause problem
    By gilgalbiblewhee in forum ASP Development
    Replies: 6
    Last Post: August 18th, 2004, 10:43 PM
  3. Query multiple tables
    By Snappy Larry in forum Microsoft Access Help
    Replies: 9
    Last Post: May 31st, 2004, 06:38 PM
  4. Crosstable query returns too many records
    By Palsam in forum ASP Development
    Replies: 7
    Last Post: March 12th, 2004, 09:07 AM
  5. Crosstable query returns too many records
    By Palsam in forum Microsoft Access Help
    Replies: 4
    Last Post: March 12th, 2004, 08:56 AM

IMN logo majestic logo threadwatch logo seochat tools logo