SitePoint Sponsor

User Tag List

Results 1 to 11 of 11

Thread: Multiple Tables

  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    Baltimore
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple Tables

    I just received a database schema from our dba and was given the task of doing a look up of several fields & then doing several specific tasks, depending on the information.

    I know how to do a lookup and an if statement for 1 table, but how do I handle a lookup in multiple tables and also do an if statement based on multiple tables?

    For example, there would be a customerid, which is in the customer table; support is in the support table; and customertype in the type table.

    Thanks

  2. #2
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you post the database schema? What is it youe looking for, SQL?

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    Baltimore
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the schema was in a visio format & i'm out of the office today, so i don't have access to it. but yes, i'm using sql for the lookup.

    i have done lookups on fields that are in 1 table, so that isn't a problem. the problem is that i've never done a lookup on multiple tables and how it would apply to an if statement.




    Quote Originally Posted by dhtmlgod
    Can you post the database schema? What is it youe looking for, SQL?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    moved to databases forum

    jleggett, do you have table and field names we could look at to help build the query?

    it'll involve a few joins, i think...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    Baltimore
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    here are the 3 primarily criterias


    1. table = clients & the field is clientid
    2. table = customer & the field is custmertype
    3. table = support & the field is supportending



    does that help? i'm going off of memory as I don't have them sitting in front of me.


    Quote Originally Posted by r937
    moved to databases forum

    jleggett, do you have table and field names we could look at to help build the query?

    it'll involve a few joins, i think...

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jleggett
    does that help?
    not a lot

    something like this??
    Code:
    select transactions.id
         , transactions.purchasedate
         , transactions.amount
         , clients.name
         , customertypes.customertype
         , support.supportending
      from transactions
    inner
      join clients
        on transactions.client_id = clients.id       
    inner
      join customertypes
        on transactions.customertype_id = customertypes.id 
    inner
      join support
        on transactions.support_id = support.id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    Baltimore
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry that i couldn't provide more information, i'll try this out tonight and will if i am unable to get it to wrok, i'll provide the schema tomorrow.

    but just to make sure, i would open each of the tables, lookup the information in the field, then would i use the statement below to generate the if statement? b/c i have several criteria that will be used to provide information and they are in 3 different tables.

    thanks again for the help.


    Quote Originally Posted by r937
    not a lot

    something like this??
    Code:
    select transactions.id
         , transactions.purchasedate
         , transactions.amount
         , clients.name
         , customertypes.customertype
         , support.supportending
      from transactions
    inner
      join clients
        on transactions.client_id = clients.id       
    inner
      join customertypes
        on transactions.customertype_id = customertypes.id 
    inner
      join support
        on transactions.support_id = support.id

  8. #8
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    Baltimore
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    here is what i've got thus far:

    <%
    Dim connection, recordset
    Dim SQL, sConnString

    SQL = "Select * From Customers where ClientID = '" & request.Form("ClientID") & "'"
    'Response.Write(SQL)

    sConnString = "Driver=server connection;"

    Set connection = Server.CreateObject("ADODB.Connection")
    Set recordset = Server.CreateObject("ADODB.Recordset")

    connection.open sConnString

    recordset.open SQL,connection

    do while NOT recordset.EOF

    If recordset("ClientID") = request.Form("ClientID") then
    Response.Write("Client ID: " & recordset("ClientID") & "<br>")
    Response.Write"Your client id was found"
    Response.Write("<br>")
    end if

    If recordset.eof then
    response.Write "There were no records returned"
    end if

    recordset.MoveNext
    loop

    Recordset.Close
    Set Recordset = nothing
    Connection.Close
    Set Connection = nothing
    %>


    the additional tables that need to be opened are Customers & from there I need to get information from the field clientype & then I also from the table Support & need to get information from the field supportexpiration.

    The question is how do I do an If statement w/ multiple tables?

    so for ex:
    If recordset("ClientID") = request.Form("ClientID") = "" and then "supportexpiration" then

    msg.

    end if



    Thanks for the help.


    Quote Originally Posted by jleggett
    sorry that i couldn't provide more information, i'll try this out tonight and will if i am unable to get it to wrok, i'll provide the schema tomorrow.

    but just to make sure, i would open each of the tables, lookup the information in the field, then would i use the statement below to generate the if statement? b/c i have several criteria that will be used to provide information and they are in 3 different tables.

    thanks again for the help.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    okay, two remarks: you would need to show the actual table and column names in order for us to write the SQL for you, and secondly, what's the IF for?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    Baltimore
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There are 3 different fields that i'm looking up. As of right now, all 3 are coming from the same table called Customers. The 3 fields that are in the Customers table are ClientID; AccountMGRID; SupportExpires.

    I have the following sql select statement of:
    SQL = "Select * From Customers where ClientID = '" & request.Form("ClientID") & "'"

    but what do i add to get the other 2 fields to be selected as well?

    Also, the if statement is to give different responses depending on, for example, if the client id is found, then a message is provided, but if it isn't found in the database, then the client's account rep's name can be provided.
    But for several of the If statements, there will have to be multiple options, i.e.

    If recordset("ClientID") = request.form("ClientID") and (AccountMGRID) <> "" then

    and so forth. Is that a little bit more clearer?






    Quote Originally Posted by jleggett
    here is what i've got thus far:

    <%
    Dim connection, recordset
    Dim SQL, sConnString

    SQL = "Select * From Customers where ClientID = '" & request.Form("ClientID") & "'"
    'Response.Write(SQL)

    sConnString = "Driver=server connection;"

    Set connection = Server.CreateObject("ADODB.Connection")
    Set recordset = Server.CreateObject("ADODB.Recordset")

    connection.open sConnString

    recordset.open SQL,connection

    do while NOT recordset.EOF

    If recordset("ClientID") = request.Form("ClientID") then
    Response.Write("Client ID: " & recordset("ClientID") & "<br>")
    Response.Write"Your client id was found"
    Response.Write("<br>")
    end if

    If recordset.eof then
    response.Write "There were no records returned"
    end if

    recordset.MoveNext
    loop

    Recordset.Close
    Set Recordset = nothing
    Connection.Close
    Set Connection = nothing
    %>


    the additional tables that need to be opened are Customers & from there I need to get information from the field clientype & then I also from the table Support & need to get information from the field supportexpiration.

    The question is how do I do an If statement w/ multiple tables?

    so for ex:
    If recordset("ClientID") = request.Form("ClientID") = "" and then "supportexpiration" then

    msg.

    end if



    Thanks for the help.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jleggett
    Is that a little bit more clearer?
    sorry, no, not for me, i'm totally lost

    maybe someone else can step in
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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