PBDR.COM

About   -   Contact   -   Purchase   -   Search   -   What's New

 
 
SQL Uncovered...(1)
The aim of this article is to explain in simple terms the concepts of Structured Query Language. Unfortunately, the VB programmer is often offered pitiful SQL code examples in the VB Help files. In fact even the MS Mastering VB Series documentation is of little extra help, so unless you require the simplest of queries, SQL construction is something of a mystery. In practice, SQL is usually learnt in the 'hands on' environment, either through interaction with DBA's or using database applications such as MS Access or MS SQL Server. So here I hope to explain some query examples which can be applied to real world situations other than the Northwind database!
But first, this article is not attempting to cover data access object like RDO, ADO or DAO. These objects are well documented in the VB online help and mostly rely on a good understanding of SQL as a prerequisite.

So to get started here's our example tables:

Customer Table

CustomerID(PK)

CustomerName

1

Jaz Lichy

2

Ken Howe

3

Ken Jones

4

John Smith

5

Joe Bloggs

Sales Table

SalesID(PK)

CustomerID(FK)

SalesAmount

10

1

$19.00

11

1

$22.00

12

2

$47.00

14

4

$62.50

15

15

$1000.00

The Customer table holds a list of Customers, each with a unique ID. This unique ID is called the Primary Key (PK). The Sales table contains sales data for each customer sale. The table holds a unique ID field, a customer ID field relating to the Customer table (this is a Foreign Key (FK), as it references a PK of another table) and the SalesAmount.

Now, the simplest form of SQL is to retrieve all data from a table, eg:

SELECT * FROM Customer

This query simply returns all the data in the customer table, ie both CustomID and CustomerName fields and all 5 records. Not much use but a good starting point...

Supposing we wanted to see which customer sales associated with them and how much, we would need to join these 2 tables. There are two ways to achieve this, but the most common way is to use inner joins as follows:

SELECT Customer.CustomerName, Sales.SalesAmount 
FROM Customer INNER JOIN Sales ON customer.CustomerID = Sales.CustomerID

The result of this query would be:

CustomerName

SalesAmount

Jaz Lichy

$19.00

Jaz Lichy

$22.00

Ken Howe

$47.00

John Smith

$62.50

Notice the result only contains data where a join exist on CustomerID in both tables.
The SQL above can be written a lot simpler, doing it this way will avoid confusion when other tables are added:

SELECT Customer.CustomerName, Sales.SalesAmount 
FROM Customer, Sales 
WHERE customer.CustomerID = Sales.CustomerID

However, although this may be simpler and the inner join format is easier to convert to an outer join query if required. Outer joins are used to return all records from one or both tables. Assume we want to show all sales so that other results total up to the full sales total then employ outer joins. Outer joins include right joins, left joins and full outer joins. The query below return all sales, even if there isnít a valid customer associated with them:

SELECT Customer.CustomerName, Sales.SalesAmount FROM Customer 
RIGHT JOIN Sales ON Customer.CustomerID = Sales.CustomerID

Our results will look like this:

CustomerName

SalesAmount

Jaz Lichy

$19.00

Jaz Lichy

$22.00

Ken Howe

$47.00

John Smith

$62.50

 

$1000.00

Notice we chose to select all the data from the right table in our join statement. This result is useful to highlight omissions and returns Null fields where an inner join would return nothing. What would it look like if we changed to this:

SELECT Customer.CustomerName, Sales.SalesAmount FROM Customer 
LEFT JOIN Sales ON Customer.CustomerID = Sales.CustomerID

Our results:

CustomerName

SalesAmount

Jaz Lichy

$19.00

Jaz Lichy

$22.00

Ken Howe

$47.00

Ken Jones  
John Smith

$62.50

Joe Bloggs  

The results gave us all the records from the left table (Customer) and the linked data from the right, which is a convenient way of reporting on customer sales.

Now, supposing we need to reconcile both tables and wish to return a left and a right join result, then we use full outer joins which return all records of these tables as follows:

SELECT Customer.CustomerName, Sales.SalesAmount FROM Customer 
FULL OUTER JOIN Sales ON Customer.CustomerID = Sales.CustomerID

This would therefore return:

CustomerName

SalesAmount

Jaz Lichy

$19.00

Jaz Lichy

$22.00

Ken Howe

$47.00

Ken Jones  
John Smith

$62.50

Joe Bloggs  
 

$1000.00


In the next article we'll take a look at ordering, grouping, summing, various search queries.
 

Top of Page

Legal Notice

© Ken Howe 2011