ASP SELECT JOIN

zhaozj2021-02-12  174

ASP SELECT JOIN

Category: Network Programming Update Date: 2004-2-8 22:41:48 Reading: 353 Join Join Connecting Field Records in Two Tables, including three: inner Join operations: connection combination two tables Field records. LEFT JOIN Operation: Connect the field record in two tables, and will contain all records in the left table of the left table. Right Join Operation: Connect the field record in the two tables, and will contain all the records in the right table in Right Join. INNER JOIN Sets two table-related operations to connect to the field records in two tables. The Inner Join syntax is as follows: from table 1 INNER JOIN Table 2 ON Table 1. Field 1 Compare Operator Table 2. Field 2 Two Table Connection Fields, such as [Table 1. Field 1 = Table 2. Field 2], must have The same field type, but the field name does not require the same. For example, the automatic number field type can connect to the field type of the LONG, but the single-intensity of the single field type cannot connect the fiddle-integrated field type. The comparative operation operator can be =, <,>, <=,> =, or <>. The field of the join connection cannot contain the MEMO field type or OLE object type, otherwise an error will occur. In a Join expression, you can connect multiple ON clauses: SELECT FIELDS FROM Table 1 Inner Join table 2 ON Table 1. Field 1 Comparative Operator Table 2. Field 1 And ON Table 1. Field 2 Compare Operator Table 2 Field 2) OR ON Table 1. Field 3 Compare Operator Table 2. Field 3) Join Expression, can be a nest: SELECT FIELDS FROM Table 1 Inner Join (Table 2 Inner Join [(] Table 3 [Inner Join [(] Table X [Inner Join ...) ON Table 3. Field 3 Compare Operator Sub Table X. Field x)] ON Table 2. Field 2 Compare Operator Table 3. Field 3) ON Table 1. Field 1 Comparative Operator Table 2. Field 2 In an Inner Join, the nest-like Left Join or Right Join, but cannot include nest-like INNER JOIN in one Left Join or Right Join. Let us see an example of using this SQL instruction among the ASP program.

You can use Inner Join ... ON to combine records in two tables, such as ASP program RS26.asp, [SELECT sales. Code, product. Name, product. Price, sales. Quantity from sale inner Join product ON product. Code = Sales. Code], use Inner Join ... ON to set two table-related operations, combine the records in two tables: <% set conn1 = server.createObject ("adodb.connection" conn1.open "Dbq =" & server.mappath ("ntopsamp.mdb") & "; driver = {Microsoft Access driver (* .mdb)}; driverid = 25; FIL = MS Access;" SET RS3 = Server.createObject ("adoDB .Recordset ") SQL =" SELECT sales. Code, product. Name, product. Price, sales. Quantity from sale Inner Join Products ON Products. Code = Sales. Code "RS3.Open SQL, CONN1, 1, 1% >

> code name price Quantity <% do while not rs3.eof%>
<% = RS3 (0)%> <% = rs3 (1)%> <% = rs3 (2)%> <% = rs3 (3)%> <% rs3.movenext loop rs3.close%> The above ASP program RS26.ASP,

User-end uses the result of the browser, browsing execution, display the records in the two tables, due to the sales table, there is no name field and price field, by [Inner Join product ON product. Code = sales. Code] Find the product table Name field and price field information. The above example uses the syntax of Inner Join: SELECT sales. Code, product. Name, product. Price, sales. Quantity from sale Inner Join product ON products. Code = Sales. Code results with the following use: SELECT Sales. Code, Product . Name, product. Price WHERE product. Code = Sales. Co-sales. Code LEFT JOIN / Right Join Inner Join Connection combination two tables in related field records, for internal connections, you can also use: Use the Left Join operation: to create a left external connection. In addition to obtaining the related field records in the two tables, Left Join will contain all records in the left table of the Left Join, whether there is a record in the right table. Use the Right Join operation: Create a right external connection. The Right Join includes all relevant field records in the two tables and will contain all the records in the right of the Right Join, regardless of whether there is a record in the left table. For example, [Sales LEFT JOIN Product] can choose all sales records. [Sales Right Join Product] You can choose all product records.

For example, using Left Join ... ON, in addition to obtaining the relevant field records in the two tables, and contains all records in the left table of Left Join, the ASP program RS26.asp is as follows, [SELECT sales. Code, product. Name, product Price, sales. Quantity from sale LEFT JOIN product ON product. Code = Sales. Code], using left join contains all records in the left side of Left Join: <% set conn1 = server.createObject ("adodb.connection" ) Conn1.open "DBQ =" & Server.mAppath ("Ntopsamp.mdb") & "; driver = {Microsoft Access Driver (* .mdb)}; driverid = 25; FIL = MS Access;" Conn1.Open "Driver = {SQL Server}; server = (local); uid = sa; pwd =; database = ntopsamp "set = server.createObject (" adoDb.recordset ") SQL =" SELECT sales. Code, product. Name, product. Price, sales. Quantity from sale Left Join product ON products. Code = sale. Code "RS3.Open SQL, CONN1, 1, 1, 1%>

code name price number <% do while not rs3.eof%>
<% = rs3 (0)%> <% = rs3 (1)%> <% = rs3 (2)%> <% = rs3 (3)%>

<% rs3.movenext loop rs3.close%> The above ASP program RS26.asp, uses the browser, browsing the results in the user, display all the sales tables in the left side of the Left Join Record, because there is no name field and price field in the sales table, the sales record of the code 4, through [Sales Left Join Products ON product. Code = sale. Code] Find the product table name field and price field information, other code There is no information on the name field and price field.

Using Right Join ... On, in addition to obtaining the related field records in the two tables, and contains all records in the right of Right Join, such as the ASP program RS26.asp, [SELECT sales. Code, product. Name, product Price, sales. Quantity from sale Right Join Products ON products. Code = Sales. Code], using Right Join contains all records in Right Join Right Product Table: <% SET CONN1 = Server.createObject ("Adodb.Connection" CONN1.OPEN "DBQ =" & Server.mAppath ("Ntopsamp.mdb") & "; driver = {Microsoft Access Driver (* .mdb)}; driverid = 25;" SET RS3 = Server. CreateObject ("AdoDb.Recordset") SQL = "SELECT sales. Code, product. Name, product. Price, sales. Quantity from sale Right Join product ON product. Code = sales. Code" RS3.Open SQL, CONN1, 1, 1,1%>

code name Price number <% do while not rs3 .Eof%>
<% = rs3 (0)%> <% = rs3 (1)%> <% = rs3 (2)%> < % = RS3 (3)%> <% rs3.movenext loop rs3.close%> The above ASP program RS26.asp,

转载请注明原文地址:https://www.9cbs.com/read-7117.html

New Post(0)
CopyRight © 2020 All Rights Reserved
Processed: 0.041, SQL: 9