Using SELECT-SQL syntax in ASP's ADO (vi)

Child query

In a SELECT, select ... Into, INSERT ... Into, DELETE, or update expressions, you can include a select expression called a subquery (sub query).

You can create subqueries using three syntaxes:

expression [Any | All | SOME] (subquery)

expression [NOT] in (subquery)

[NOT] EXISTS (subquery)

A select expression of a subquery, identical to the syntax of a general select expression, must be included in parentheses.

You can use subqueries to override the expressions in a select expression, or in a where or a HAVING clause.

The keyword any and some have the same meaning and are used to select the comparison criteria for any record that conforms to the subquery. For example, the following example returns a record in a product that is greater than any quantity in the order that is greater than 100:

SELECT * FROM Product

WHERE Price > any

(SELECT unit price from order

WHERE Quantity > 100)

Keyword all, which is used to select comparison criteria for all records that meet the subquery.

For example, in the previous example, the any is changed to all, and the unit price in the product is greater than the record for all quantities greater than 100 in the order.

keyword in discourse to retrieve some records in the main query that contain the same value only in the subquery. The following example returns all products sold at a discount of 25 or higher:

keyword in, which is used to select records in the subquery. For example, the following example returns a record of the number > 100 in the Order:

SELECT * FROM Product

WHERE Product Code name in

(SELECT Product code from order

WHERE Quantity > 100)

Instead, the keyword isn't in, which is used to select records that aren't in the subquery.

In True/false comparisons, you can use the EXISTS keyword to determine whether a subquery will return any records.

ASP examples of keyword all, such as ASP program rs24.asp, [select Name, account, score from exam where subject = ' arithmetic ' and score >= all (select score from test where account = ' arithmetic ') and name = ' John ')] find the arithmetic record of the arithmetic examination with a score greater than or equal to John:

<%

Set conn1 = Server.CreateObject ("ADODB. Connection ")

Conn1. Open "dbq=" & Server.MapPath ("Ntopsamp.mdb") & ";D river={microsoft Access Driver (*.mdb)};D riverid=25;fil=ms Access; "

Set rs2 = Server.CreateObject ("ADODB. Recordset ")

sqlstr = "Select name, account, score from exam where account = ' arithmetic ' and score >= all (select fraction from exam where account = ' arithmetic ' and name = ' John ')"

Rs2. Open sqlstr,conn1,1,1

Response.Write "<p>all above John arithmetic all fractions"

Do as not rs2. Eof

Response.Write "<BR>" & Rs2 ("name") & "& Rs2 (" Account ") &" Score: "& Rs2 (" Score ")

Rs2. MoveNext

Loop

Rs2. Close

%>

The above ASP program rs24.asp, uses the browser in the client side, browses the execution result, displays the arithmetic record which the score is greater than or is equal to John's arithmetic examination.

Any

The keyword any is used to select comparison criteria for any record that conforms to the subquery, such as the ASP program rs24.asp as follows, [select Name, account, score from test Where account = ' arithmetic ' and score >= any (select score from Test W Here account = ' arithmetic ' and name = ' John '] find records with fractions greater than or equal to John any arithmetic fractions:

<%

Set conn1 = Server.CreateObject ("ADODB. Connection ")

Conn1. Open "dbq=" & Server.MapPath ("Ntopsamp.mdb") & ";D river={microsoft Access Driver (*.mdb)};D riverid=25;fil=ms Access; "

Set rs2 = Server.CreateObject ("ADODB. Recordset ")

sqlstr = "Select name, account, score from exam where account = ' arithmetic ' and score >= any (select fraction from test where account = ' arithmetic ' and name = ' John ')"

Rs2. Open sqlstr,conn1,1,1

Response.Write "<p>any above John arithmetic any score"

Do as not rs2. Eof

Response.Write "<BR>" & Rs2 ("name") & "& Rs2 (" Account ") &" Score: "& Rs2 (" Score ")

Rs2. MoveNext

Loop

Rs2. Close%>

The above ASP program rs24.asp, uses the browser in the client side, browses the execution result, displays the score is greater than or is equal to John any arithmetic score record.

Some

Keyword Some and any have the same meaning to select the comparison criteria for any records that match the subquery, such as the ASP program rs24.asp as follows, [select Name, account, score from test Where account = ' arithmetic ' and score >= Some (select Score from test Where account = ' arithmetic ' and name = ' John '] find records with fractions greater than or equal to John any arithmetic fractions:

<%

Set conn1 = Server.CreateObject ("ADODB. Connection ")

Conn1. Open "dbq=" & Server.MapPath ("Ntopsamp.mdb") & ";D river={microsoft Access Driver (*.mdb)};D riverid=25;fil=ms Access; "

Set rs2 = Server.CreateObject ("ADODB. Recordset ")

sqlstr = "Select name, account, score from exam where account = ' arithmetic ' and score >= Some (select fraction from test where account = ' arithmetic ' and name = ' John ')"

Rs2. Open sqlstr,conn1,1,1

Response.Write "<p>some above John arithmetic any score"

Do as not rs2. Eof

Response.Write "<BR>" & Rs2 ("name") & "& Rs2 (" Account ") &" Score: "& Rs2 (" Score ")

Rs2. MoveNext

Loop

Rs2. Close

%>

The above ASP program rs24.asp, uses the browser in the client side, browses the execution result, displays the score is greater than or is equal to John any arithmetic score record.

Select ... Into

Select ... into will query the results of the creation of a generated table.

The syntax is as follows:

SELECT field 1[, Field 2[, ...] into new table [in external table]
From table

The name of the new table cannot be the same as the name of the existing table, or an error will occur.

Select ... Into the new table that is created with the same data type and size as the table you are querying.

Let's look at an example of using this SQL instruction in an ASP program.

For example, ASP program rs9.asp as follows, [Select * into computer from product Where kind = ' computer '] will produce a new [computer] table for all [kinds] of [computer] records in the [Products] table:

<%

Set conn1 = Server.CreateObject ("ADODB. Connection ")

Conn1. Open "dbq=" & Server.MapPath ("Ntopsamp.mdb") & ";D river={microsoft Access Driver (*.mdb)};D riverid=25;fil=ms Access; "

sql = "SELECT * into computer from product Where kind = ' computer '"

Set a = conn1. Execute (SQL)

Set RS3 = Server.CreateObject ("ADODB. Recordset ")

sql = "SELECT * from Computer"

Rs3. Open sql,conn1,1,1,1

%>

<table colspan=8 cellpadding=5 border=0>

<TR>

<TD align=center bgcolor= "#800000" ><font color= "#FFFFFF" > Code </FONT></TD>

<TD align=center bgcolor= "#800000" ><font color= "#FFFFFF" > Name </FONT></TD>

<TD align=center bgcolor= "#800000" ><font color= "#FFFFFF" > Price </FONT></TD>

<TD align=center bgcolor= "#800000" ><font color= "#FFFFFF" > Quantity </FONT></TD>

</TR>

<% do, not RS3. EOF%>

<TR>

<TD bgcolor= "F7efde" align=center><%= rs3 ("code")%></td>

<TD bgcolor= "F7efde" align=center><%= rs3 ("name")%></td>

<TD bgcolor= "F7efde" align=center><%= rs3 ("Price")%></td>

<TD bgcolor= "F7efde" align=center><%= rs3 ("Quantity")%></td>

</TR>

<%

Rs3. MoveNext

Loop

Rs3. Close

%>

</TABLE>

The above ASP program rs9.asp, uses the browser in the client side, browses the execution result, displays the new [computer] table record.