Sub Queries and Sub Tables in SQL and VB

Introduction

Welcome to today's article. Today I want to demonstrate what sub queries and sub tables are, and how to make use of them from within Visual Basic.

Before you can jump in with the inner-workings of today's topic, you will need some background information.

Databases

Databases can be friendly creatures if you know how to use them correctly; they also can become very bad, especially if you have no idea on how to use them properly. A database's sole function is to store information, hence the name: data base. Knowing how to properly store inside them takes practice, and lots of planning. People who fail to this get eaten up by the database monster.

A database consist of the following objects:

  • Tables
  • Queries
  • Views
  • Stored Procedures
  • Functions

Tables

Tables are the main building blocks of any database. This is where all the information will be stored. A table consists of rows (records) and columns (fields). A row, or record, contains all the information pertaining to one topic. A column, or field, is once piece of information.

SQL

Structured Query Language (SQL)is the programming language you use to build databases, extract information from databases, and store information into databases. In an earlier article, I spoke about the term database, so if you haven't read it yet, have a read here. If this is your first time hearing the term SQL, have a look here.

To extract any information from any database we need to write an SQL query. A query can be explained as a request for information which it then presents ultimately. Now, as I have mentioned, all database information gets stored inside tables. These tables contain all the information that has been stored. We must write a query to get this information and present it to the user.

Queries

The simplest form of a query would look like the following:

SELECT * FROM TableName

This will give you all the data stored in that particular table. Remember now, that some tables can have millions of records inside them, so, in this case, this simple query can give you all the millions of records. Usually, this is not really needed. Usually, there is some sort of condition involved when extracting information. A query with a condition will look like this:

SELECT * FROM TableName WHERE Field = Value

A query similar to the above now has the capability to return only certain information. Let me use a bank as an example. With any bank, they deal with millions of customers. Many of these customers may have the same last names, or even the exact same full names. Any customer of the bank may have more than one type of account. Now, taking all of this into consideration, a query to return all the information to a specific person may be troublesome. If conditions did not exist, this would have caused you to stand in the banking queue for a very long time while the poor teller sifts through all the information just to find the correct person with the correct information.

We can take conditions further, and write a query like the following:

SELECT * FROM TableName WHERE Field1 = Value1
   AND Field2 > Value2 OR Field3 < Value3

It gets trickier now, as here we deal with more than one field's value and more than one condition. The above query can be translated into layman's terms to mean:

Select all the data from the table named TableName where Column1 is equal to Value1 AND Column2 is greater than Value2 OR Column3 is less than Value3.

It can get more complicated...

SELECT * FROM Table1, Table2, Table3
   WHERE Table1.Field1 = Value1
   AND Table2.Field2 > Value2
   OR Table3.Field3 < Value3

This SQL code extracts certain data from three different tables, based on various conditions set.

Sub Queries

Have a read through the following links:

Here is an example:

SELECT
   Table1.Field1, Table1.Field2,
   (SELECT Field1 FROM Table2
      WHERE Table2.Field = Table1.Field)
      AS [Code],
   (SELECT Field2 FROM Table3
      WHERE Table3.Field = Table1.Field)
      AS [Course]

FROM Table1

The preceding query selects two fields from a table. Then it makes use of a sub query to select a corresponding field from a secondary table, and then a third table. So, as you can see, a sub query is a very useful tool to extract data from other tables without the need of a join. For more information regarding Joins, have a read through here:

Sub Tables

Have a read through here to get an understanding of sub tables.

An example follows:

SELECT
[Field 1], [Field 2], [Field 3], [Field 4]
FROM
   (SELECT
   Table1.Field1 AS [Field 1],
   Table1.Field2 AS [Field 2],
   ((SELECT Field1 FROM Table2
      WHERE Table2.Field = Table1.Field))
      AS [Field 3],
   ((SELECT Field2 FROM Table2
      WHERE Table2.Field = Table1.Field))
      AS [Field 4]
FROM Table1
) AS [GROUPEDTABLE]
WHERE [GROUPEDTABLE].Field1 = 'VALUE'
GROUP BY [Field 1], [Field 2], [Field 3],
   [Field 4]

In the example above, a sub table is created mostly for grouping purposes. Grouping in this case means that when you have large groups of similar data, you can Group them according to their values. In the above case, all the redundant information should have been discarded.

A better example would have been to use an aggregate function such as Sum to sum all the information, hence providing only the calculated totals from the Sub Table. For more information about Aggregate Functions, read through here.

Our Project

Use SQL Server to create a database, and then create three tables, as shown in Figure 1. You also may add information to all the tables as you see fit.

Tables
Figure 1: Database tables

If you do not know how to create a database or database tables, I suggest you read here for further information.

Create a new query and add either of the following code into it:

SELECT
   StudentDetails.Name, StudentDetails.Surname,
   (SELECT TOP 1 CourseCode FROM CourseDetails
      WHERE CourseDetails.StudentID = StudentDetails.StudentNumber)
      AS [Code],
   (SELECT TOP 1 CourseName FROM CourseDetails
      WHERE CourseDetails.StudentID = StudentDetails.StudentNumber)
      AS [Course]

FROM StudentDetails


SELECT
   CourseDetails.CourseCode, CourseDetails.CourseName,
   (SELECT TOP 1 Name FROM StudentDetails
      WHERE StudentDetails.StudentNumber = CourseDetails.StudentID)
      AS [Code],
   (SELECT TOP 1 Surname FROM StudentDetails
      WHERE StudentDetails.StudentNumber = CourseDetails.StudentID)
      AS [Course]

FROM CourseDetails

This is the code for our sub queries. Create another query and add the following code for the sub table into it:

SELECT
[Subject Code], [Subject Description], [Code] AS [Course Code], [Course]
FROM

   (SELECT
   SubjectDetails.SubjectCode AS [Subject Code],
   SubjectDetails.SubjectDescription AS [Subject Description],
   ((SELECT TOP 1 CourseCode FROM CourseDetails
      WHERE CourseDetails.CourseID = SubjectDetails.CourseID))
      AS [Code],
   ((SELECT TOP 1 CourseName FROM CourseDetails
      WHERE CourseDetails.CourseID = SubjectDetails.CourseID))
      AS [Course]

FROM SubjectDetails


) AS [CD]

WHERE [CD].Code = 'VB'
GROUP BY [Code], [Course], [Subject Code], [Subject Description]

Create a new VB Project, add a Datagridview to the form, and add the following code into it:

Imports System.Data.SqlClient

Public Class Form1

    'variables
   Dim con As SqlConnection = _
      New SqlConnection("Data Source=HTG\ _
      Test;AttachDbFilename= _
      |DataDirectory|\MyDB.mdf;Integrated Security=True; _
      Connect Timeout=30;User Instance=True")
   Dim cmd As SqlCommand
   Dim myDA As SqlDataAdapter
   Dim myDataSet As DataSet

   Private Sub Form1_Load(ByVal sender As System.Object, _
         ByVal e As System.EventArgs) Handles MyBase.Load
      ShowSubQuery1()
   End Sub

   'Bind database to DataGridView
   Public Sub ShowSubQuery1()
      cmd = New SqlCommand("SELECT StudentDetails.Name, _
         StudentDetails.Surname, (SELECT TOP 1 CourseCode _
         FROM CourseDetails WHERE CourseDetails.StudentID = _
         StudentDetails.StudentNumber) AS [Code], _
         (SELECT TOP 1 CourseName FROM CourseDetails _
         WHERE CourseDetails.StudentID = StudentDetails.StudentNumber) _
         AS [Course] FROM StudentDetails ", con)
      If con.State = ConnectionState.Closed Then con.Open()
      myDA = New SqlDataAdapter(cmd)
      myDataSet = New DataSet()
      myDA.Fill(myDataSet, "MyTable")
      DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView
   End Sub

   'Bind table to DataGridView
   Public Sub ShowSubTable()
      cmd = New SqlCommand("SELECT [Subject Code], [Subject Description], _
         [Code] AS [Course Code], [Course] _
         FROM (SELECT SubjectDetails.SubjectCode _
         AS [Subject Code], SubjectDetails.SubjectDescription _
         AS [Subject Description], ((SELECT TOP 1 CourseCode _
         FROM CourseDetails WHERE CourseDetails.CourseID = _
         SubjectDetails.CourseID)) AS [Code], ((SELECT TOP 1 CourseName _
         FROM CourseDetails WHERE CourseDetails.CourseID = _
         SubjectDetails.CourseID)) AS [Course] FROM SubjectDetails) _
         AS [CD] WHERE [CD].Code = 'VB' GROUP BY [Code], [Course], _
         [Subject Code], [Subject Description]", con)
      If con.State = ConnectionState.Closed Then con.Open()
      myDA = New SqlDataAdapter(cmd)
      myDataSet = New DataSet()
      myDA.Fill(myDataSet, "MyTable")
      DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView
   End Sub

   ' Retrieve/Select records
   Private Sub Button1_Click(ByVal sender As System.Object, _
         ByVal e As System.EventArgs) Handles Button1.Click
      cmd = New SqlCommand("SELECT CourseDetails.CourseCode, _
         CourseDetails.CourseName, (SELECT TOP 1 Name _
         FROM StudentDetails WHERE StudentDetails.StudentNumber = _
         CourseDetails.StudentID) AS [Name], (SELECT TOP 1 Surname _
         FROM StudentDetails WHERE StudentDetails.StudentNumber = _
         +/CourseDetails.StudentID) AS [Surname] FROM CourseDetails", con)
      If con.State = ConnectionState.Closed Then con.Open()
      Dim sdr As SqlDataReader = cmd.ExecuteReader()
      While sdr.Read = True
         MessageBox.Show(sdr.Item("CourseCode") & " " & sdr.Item("Name"))
      End While
      sdr.Close()
   End Sub

   Private Sub Button2_Click(sender As Object, e As EventArgs) _
          	Handles Button2.Click
      ShowSubTable()
   End SubEnd Class

First, I created the connection to the SQL Server table and stored inside the con variable.

In all the various subs, I have made use of the SQLCommand function to populate the SQL query string, and from there, the associated data gets loaded into the Data Adapters that load the resulting data into the Datagridview.

Conclusion

As you can see, working with sub queries and sub tables isn't that difficult in SQL and VB. Until next time, good bye.



Related Articles

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date