CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
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:
- https://technet.microsoft.com/en-us/library/ms189575%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396
- https://technet.microsoft.com/en-us/library/ms187638%28v=sql.105%29.aspx
- https://technet.microsoft.com/en-us/library/ms175838%28v=sql.105%29.aspx
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:
- https://technet.microsoft.com/en-us/library/ms191517%28v=sql.105%29.aspx
- https://technet.microsoft.com/en-us/library/ms191472%28v=sql.105%29.aspx
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.
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.
