How to Create Nested Queries in Visual Basic 3.0 Prof Edition

   UPDATE Orders SET Orders.ItemCount = (SELECT COUNT(*) WHERE
      Orders.OrderNum = Item.OrderNum)
				

   UPDATE Orders SET Orders.ItemCount =
      DCount('*'','Orders','Orders.OrderNum = Item.OrderNum')
				

MORE INFORMATION

NOTE: The following queries are based on the sample database (BIBLIO.MDB) that comes with Visual Basic version 3.0. BIBLIO.MDB contains entries on actual reference works dealing with database programming.

For example, subqueries are useful when you need to find all the books that were published the same year as the book, "The database experts' guide to SQL." You could do this in two steps. First, find out what year that the book was published:

   SELECT DISTINCTROW [year published] from titles
      WHERE titles.title = 'The database experts'' guide to SQL'
      WITH OWNERACCESS OPTION;
				

Then take the result (1988) and build a second query based on this result:

   SELECT DISTINCTROW  title,[year published] from titles
      WHERE titles.[year published] = 1988
      WITH OWNERACCESS OPTION;
				

Microsoft SQL Server syntax would support the combination of these two queries into one query with a subquery:

   SELECT  DISTINCT title,[year published]
      From titles
      WHERE titles.[year published] =
      (Select  [year published] from titles
      WHERE titles.title = 'The database experts'' guide to SQL' )
				

However, the Microsoft Access engine cannot parse this directly, so the alternative is to create a QueryDef in a Microsoft Access-format database; then reference this querydef in a succeeding query. This nesting can be of multiple levels. However, all the nested queries must be resolved in order to return the result from the outer or topmost query. Therefore, you may find that at some multiple level of nesting, performance will become unacceptable or the engine's internal workspace capacity will be exceeded.

NOTE: Even if the data sources are not Microsoft Access databases (.MDB files), the performance gains from making the data sources into attached tables in a .MDB database as well as the added benefit of being able to create and store querydefs, makes the use of even a data-free .MDB file a compelling choice. The .MDB file could be used to hold only the TableDefs of attached tables and QueryDefs, whether permanently stored or created on the fly during the execution of your program.

Visual Basic Code -- Example One

Here's an illustration of the Visual Basic solution to the limitation on subqueries. The following shows the code that can handle the specific example referenced above by using a nested query. The goal is to obtain all the books published the same year as the book "The database experts' guide to SQL."

Dim db As database, ds As Dynaset, qd As QueryDef
Set db = OpenDatabase("C:\VB\BIBLIO.MDB")

' Formulate subquery. Enter all three lines as one, single line of code:
subq$ = "SELECT DISTINCTROW  [year published] from titles
   where titles.title = 'The database experts'' guide to SQL'
   WITH OWNERACCESS OPTION;"

' NOTE: Because the book title contains an embedded single quotation mark
' or apostrophe, note the use of doubled apostrophes in the book title
' string literal to avoid confusing the SQL parser.

' For testing purposes, delete any existing QueryDef, and change the
' first run db.DeleteQueryDef ("Year Book Was Published") into a comment.
' Next, create a QueryDef in the BIBLIO.MDB:
Set qd = db.CreateQueryDef("Year Book Was Published", subq$)

' Now, the following SQL statement will obtain the desired results.
' Enter the following five lines as one, single line in Visual Basic:
Set ds = db.CreateDynaset("SELECT DISTINCTROW
   title,titles.[year published]
   from titles , [Year Book Was Published] where titles.[year published] =
   [Year Book Was Published].[year published]
   WITH OWNERACCESS OPTION;")
				

Notice that the column or field name returned by the QueryDef, [year published], is available as a valid field reference in the Where clause of the outer query. If the column names are aliased, using the <columnname> As <aliasname> syntax, then the aliasname must be used by the outer query when referring to columns returned by the query, as in Example Two.

Visual Basic Code -- Example Two

Dim db As database, ds As Dynaset, qd As QueryDef

Set db = OpenDatabase("E:\PROGDIR\VB3\BIBLIO.MDB")

' Enter the following four lines as one, single line:
s$ = "select authors.au_id as temp1,authors.author as temp2,
   titles.title as temp3, titles.pubID as temp4 from authors,titles,
   authors inner join titles on authors.au_ID=titles.au_ID
   order by authors.author"

' For testing purposes, delete any existing QueryDef. Turn the following
' into a comment line on the first run:
db.DeleteQueryDef ("Nested")

Set qd = db.CreateQueryDef("Nested", s$)

' Now build a query based on the columns in the inner nested QueryDef.
' Enter the following two lines as one, single line of code:
Set ds = db.CreateDynaset("select temp1, temp2, temp3, publishers.name from
   publishers, Nested where publishers.pubid=temp4 order by temp2")
				

Same Queries Without the Use of Aliases

The same queries without the use of aliases would be as follows. Note that you need to enter each query as one, single line:

' Enter the following three lines as one, single line:
s$ = "select authors.au_id ,authors.author , titles.title , titles.pubID
   from authors,titles, authors inner join titles on
   authors.au_ID=titles.au_ID order by authors.author"

' For testing purposes, delete any existing QueryDef. Turn the following
' into a comment line on the first run:
db.DeleteQueryDef ("Nested")

Set qd = db.CreateQueryDef("Nested", s$)

' Now build a query based on the columns in the inner nested QueryDef.
' Enter the following three lines as one, single line of code:
Set ds = db.CreateDynaset("select authors.au_id,authors.author,
   titles.title,publishers.name from publishers,
   Nested where publishers.pubid=titles.pubid order by authors.author")
				

Getting the Results of a Non-Existence Query

A more challenging application is to get the results of a non-existence query. For example, if you needed to find out all the publishers who have no books in the database that were published in the year 1988, some form of subquery would be needed. Therefore, you could do it using nested queries in the Microsoft Access engine in Visual Basic version 3.0 and Microsoft Access version 1.1.

The first QueryDef is built on the following SQL statement:

   SELECT DISTINCTROW titles.title, titles.[year published],titles.pubid
      FROM titles
      WHERE titles.[year published]= 1988
      WITH OWNERACCESS OPTION;
				

This returns those book titles, along with their associated pubid field, that were published in 1988. Next, the following outer join (Left Join in Microsoft Access syntax), allows you to look for non-existence, in the form of Nulls in the result set:

   SELECT DISTINCTROW publishers.pubid, publishers.name
      FROM publishers, [titles in 1988],
      publishers LEFT JOIN [titles in 1988]
      ON publishers.pubid = [titles in 1988].pubid
      WHERE [titles in 1988].pubid Is Null
      WITH OWNERACCESS OPTION;
				

Visual Basic Code -- Example Three

NOTE: Place the command button Command1 in the lower left corner of the form to allow room for the printing to the form.

Sub Command1_Click ()
Dim db As database
Dim ds As Dynaset
Dim qd As QueryDef
Dim NL$, Tabb$, subq$, query$

NL$ = Chr$(13) & Chr$(10)
Tabb$ = Chr$(9) & Chr$(9)

Set db = OpenDatabase("E:\PROGDIR\VB3\BIBLIO.MDB")

' Formulate the query that selects titles published in 1988.
' Enter the following three lines as one, single line of code:
subq$ = "SELECT DISTINCTROW titles.title, titles.[year
   published],titles.pubid FROM titles
   WHERE titles.[year published]= 1988 WITH OWNERACCESS OPTION;"

' For testing purposes, delete any existing QueryDef. Turn the following
' line into a comment on the first run:
db.DeleteQueryDef ("titles in 1988")

' Next, create the inner query:
Set qd = db.CreateQueryDef("titles in 1988", subq$)

' Print to the form the contents of the sub query for a sanity check:
Me.WindowState = 2  ' Maximize form for more room
Set ds = qd.CreateDynaset()
Print " Name : "; ds.Name
While Not ds.EOF
   For i = 0 To ds.Fields.Count - 1
      Print ds(i); Tabb$;
   Next i
   Print
   ds.MoveNext
Wend

Print NL$, NL$

' Now formulate a query based on the previous query.
' Enter the following four lines as one, single line of code:
query$ = "SELECT DISTINCTROW publishers.pubid, publishers.name FROM
   publishers, [titles in 1988], publishers LEFT JOIN [titles in 1988]
   ON publishers.pubid = [titles in 1988].pubid WHERE [titles
   in 1988].pubid Is Null WITH OWNERACCESS OPTION;"

' For testing purposes, delete any existing QueryDef. But turn the
' following into a comment on the first run:
db.DeleteQueryDef ("publishers who have no titles in 1988")

Set qd = db.CreateQueryDef("publishers who have no titles in 1988", query$)
Set ds = qd.CreateDynaset()

' Print to the form the contents of the outer query:
Print " Name : "; ds.Name
While Not ds.EOF
   For i = 0 To ds.Fields.Count - 1
      Print ds(i); Tabb$;
   Next i
   Print
   ds.MoveNext
Wend

Print NL$, NL$

' Or print the contents of a Dynaset directly based on the subquery:
Set ds = db.CreateDynaset(query$)

' Enter the following two lines as one, single line of code:
Print "The results of a direct CreateDynaset with SQL referencing the
QueryDef [titles in 1988]"

Print " Name : "; ds.Name
While Not ds.EOF
   For i = 0 To ds.Fields.Count - 1
      Print ds(i); Tabb$;
   Next i
   Print
   ds.MoveNext
Wend

ds.Close
qd.Close
db.Close
End Sub