open 2 tables-1 statement

Results 1 to 2 of 2

Thread: open 2 tables-1 statement

  1. #1
    Doug G Guest

    Default open 2 tables-1 statement

    Is it possible, in a dsn-less connection to open two tables in the same db? If so how do you write the statement below to do that and have then both open to display the info in both.<BR><BR>strconn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("mydb.mdb")<BR><BR>set conn = server.createobject("adodb.connection")<BR>conn.op en strconn<BR><BR>set rs = server.createobject("adodb.recordset")<BR><BR><BR> <BR> sqlstmt = "SELECT * from table1"

  2. #2
    Join Date
    Dec 1969

    Default Time to learn a little SQL

    You can only open one RECORDSET with a single statement, but you could theoretically thus be accessing literally dozens of tables (up to whatever the limit is for the particular DB you are using). <BR><BR>But in order to use more than one table at a time, the multiple tables must be *RELATED* in some way (hence the generic term "Relational DataBase Management System"--RDBMS).<BR><BR>In a well-designed database, tables will be naturally and logically related!<BR><BR>For example, consider a catalog order database. Here are just *some* of the tables you might find there:<BR><BR>TABLE: Inventory<BR>&nbsp; &nbsp; InvID -- number, primary key<BR>&nbsp; &nbsp; InvDesc -- text<BR>&nbsp; &nbsp; InvBasePrice -- number or currency<BR>&nbsp; &nbsp; InvOnHand -- number (integer)<BR><BR>TABLE: Customers<BR>&nbsp; &nbsp; CustID -- number, primary key<BR>&nbsp; &nbsp; CustName -- text<BR>&nbsp; &nbsp; CustAddr -- text<BR>&nbsp; &nbsp; CustCity -- text<BR>&nbsp; &nbsp; CustState -- text<BR>&nbsp; &nbsp; CustZip -- text<BR>&nbsp; &nbsp; CustDiscount -- number (percentage discount?)<BR><BR>TABLE: Orders<BR>&nbsp; &nbsp; OrderID -- number, primary key<BR>&nbsp; &nbsp; CustID -- number, foreign key to Customers table<BR>&nbsp; &nbsp; OrderDate -- date/time<BR>&nbsp; &nbsp; OrderTotal -- number or currency<BR>&nbsp; &nbsp; OrderShipped -- date/time (1/1/1970 if not yet shipped)<BR><BR>TABLE: OrderItems<BR>&nbsp; &nbsp; OrderID -- number, foreign key to Orders table<BR>&nbsp; &nbsp; InvID -- number, foreign key to Inventory table<BR>&nbsp; &nbsp; Quantity -- number (integer)<BR><BR>Now those tables are far from complete for a "real" system. No partial order shipments provided for, no "ship to" address that is different from customer address. No payment method or terms allowed for. No link to the accounts receivables for possible payments to be made later. Etc., etc., etc. That&#039s just the tip of the iceberg, in fact.<BR><BR>But already hopefully you can see how you could link these tables together.<BR><BR>For example, if you wanted to print out a "pick list" of all orders not yet shipped (for the shipping department), you might do something like this:<BR><BR>SELECT Customers.*, OrderItems.Quantity, Inventory.InvID, Inventory.InvDesc, Orders.OrderDate<BR>FROM Customers, OrderItems, Inventory, Orders<BR>WHERE Orders.OrderShipped = #1/1/1970#<BR>AND OrderItems.OrderID = Orders.OrderID<BR>AND Inventory.InvID = OrderItems.InvID<BR>AND Customers.CustID = Orders.CustID<BR><BR>Do you see that? We start by finding each the records in the Orders table that have not been shipped. Then, for each such order, we find all the OrderItems records that belong to that particular record. And for each OrderItems record we find the item in Inventory that matches, in order to get its description and id to print on the "pick list". Finally, we go back to the particular Orders record to find the customer id and match the record in the Customers table so we can display the name, address, etc.<BR><BR>Since this is just a "pick list", we don&#039t bother getting the customer&#039s discount or the total amount of the order--that information is none of the business of the people in the shipping department. If we were printing an invoice for the customer, then of course we&#039d get that information as well.<BR><BR>But all of this relates much more closely to how an RDBMS works, and how you use SQL to access such a DB, than it does to ASP. So you may want to go find a beginning guide to SQL to learn more. One on-line place to start is -- the best one-HTML-page summary of SQL any place on the Web, so far as I&#039ve been able to find.<BR><BR>Good luck<BR>

Posting Permissions

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