Using SELECT syntax six in ADO

Author：Eve Cole Update Time：2009-05-30 19:54:23

subquery

In a SELECT, SELECT...INTO, INSERT...INTO, DELETE, or UPDATE expression, you can include a SELECT expression. This SELECT expression is called a subquery.

You can build subqueries using three syntaxes:

Expression [ANY | ALL | SOME] (subquery)

Expression [NOT] IN (subquery)

[NOT] EXISTS (subquery)

A SELECT expression of a subquery has the same syntax as a general SELECT expression and must be enclosed in parentheses.

You can use subqueries to replace operators in a SELECT expression, or in a WHERE or HAVING clause.

The keywords ANY and SOME have the same meaning and are used to select the comparison conditions of any records that match the subquery. For example, the following example will return records in which the unit price of the product is greater than any quantity greater than 100 in the order:

SELECT * FROM products

WHERE unit price > ANY

(SELECT unit price FROM order

WHERE quantity > 100)

The keyword ALL is used to select the comparison conditions of all records that match the subquery.

For example, in the above example, changing ANY to ALL will return records in which the unit price of the product is greater than all orders with a quantity greater than 100.

Keyword IN predicate to retrieve certain records that contain the same value in the main query and only in the subquery. The following example returns all products sold at a discount of 25 percent or more:

The keyword IN is used to select records in the subquery. For example, the following example will return records with quantity > 100 in the order:

SELECT * FROM products

WHERE product code IN

(SELECT product code FROM order

WHERE quantity > 100)

In contrast, the keyword NOT IN is used to select records that are not included in the subquery.

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

An ASP example of the keyword ALL, such as the ASP program rs24.asp is as follows, [SELECT name, subject, score From exam Where subject = 'arithmetic' and score >= All (SELECT score From exam Where subject = 'arithmetic' and name =' Zhang San')] Find the arithmetic records of the arithmetic test whose score is greater than or equal to Zhang San:

<%

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

conn1.Open "DBQ=" & Server.MapPath("ntopsamp.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"

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

SqlStr = "SELECT name, subject, score From exam Where subject = 'Arithmetic' and score >= All (SELECT score From exam Where subject = 'Arithmetic' and name = 'Zhang San')"

rs2.Open SqlStr,conn1,1,1

Response.Write "All scores higher than Zhang San's arithmetic"

Do while not rs2.EOF

Response.Write " " & rs2("Name") & " " & rs2("Subject") & " Score: " & rs2("Score")

rs2.MoveNext

Loop

rs2.Close

%>

The above ASP program rs24.asp uses a browser on the client side to browse the execution results and display the arithmetic records of the arithmetic test whose scores are greater than or equal to Zhang San.

Any

The keyword ANY is used to select the comparison conditions of any record that matches the subquery. For example, the ASP program rs24.asp is as follows, [SELECT name, subject, score From exam Where subject = 'arithmetic' and score >= Any (SELECT score From exam Where Subject='Arithmetic' and Name='Zhang San')] Find the records whose fraction is greater than or equal to any arithmetic fraction of Zhang San:

<%

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

conn1.Open "DBQ=" & Server.MapPath("ntopsamp.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"

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

SqlStr = "SELECT name, subject, score From exam Where subject = 'Arithmetic' and score >= Any (SELECT score From exam Where subject = 'Arithmetic' and name = 'Zhang San')"

rs2.Open SqlStr,conn1,1,1

Response.Write "Any fraction higher than Zhang San's arithmetic"

Do while not rs2.EOF

Response.Write " " & rs2("Name") & " " & rs2("Subject") & " Score: " & rs2("Score")

rs2.MoveNext

Loop

rs2.Close%>

The above ASP program rs24.asp uses a browser on the client side to browse the execution results and display the records whose scores are greater than or equal to any arithmetic fraction of Zhang San.

Some

The keywords SOME and ANY have the same meaning, and are used to select the comparison conditions of any record that matches the subquery. For example, the ASP program rs24.asp is as follows, [SELECT Name, Subject, Score From Exam Where Subject = 'Arithmetic' and Score >= Some (SELECT Score From Exam Where Subject='Arithmetic' and Name='Zhang San')] Find the records whose scores are greater than or equal to any arithmetic fraction of Zhang San:

<%

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

conn1.Open "DBQ=" & Server.MapPath("ntopsamp.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"

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

SqlStr = "SELECT name, subject, score From exam Where subject = 'Arithmetic' and score >= Some (SELECT score From exam Where subject = 'Arithmetic' and name = 'Zhang San')"

rs2.Open SqlStr,conn1,1,1

Response.Write "Some fraction higher than Zhang San's arithmetic"

Do while not rs2.EOF

Response.Write " " & rs2("Name") & " " & rs2("Subject") & " Score: " & rs2("Score")

rs2.MoveNext

Loop

rs2.Close

%>

The above ASP program rs24.asp uses a browser on the client side to browse the execution results and display the records whose scores are greater than or equal to any arithmetic fraction of Zhang San.

Select...Into

Select...Into will create a generated table based on the query results.

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, otherwise an error will occur.

The new table created by Select...Into has the same field data type and size as the queried table.

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

For example, the ASP program rs9.asp is as follows, [Select * Into Computer From Product Where Type = 'Computer'] will generate a new [Computer] table for all [Type] records in the [Product] table as [Computer]:

<%

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

conn1.Open "DBQ="& Server.MapPath("ntopsamp.mdb") &";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"

sql = "Select * Into Computer From Product Where Category = '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">Code</TD>

<TD ALIGN=CENTER BGCOLOR="#800000">Name</TD>

<TD ALIGN=CENTER BGCOLOR="#800000">Price</TD>

<TD ALIGN=CENTER BGCOLOR="#800000">Quantity</TD>

</TR>

<% Do while not rs3.EOF %>

<TR>

<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3("codename")%></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 a browser on the client to browse the execution results and display the records of the new [Computer] table.