SQL Uncovered...(2)

In the last SQL article we covered the basics including examples inner joins and outer joins, so we'll continue by expanding the number of tables to demonstrate multiple joins. To our original table line-up, we'll add the Contact table as follows:

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

Contact Table

CustomerID(PK)	PhoneNo
1	0123456
2	9876543
3	2460810
5	0128910

If the required results were those based on a straight forward join on all 3 tables, in the case of an inner join then the simplest way to achieve this would be as follows:

SELECT Customer.CustomerName, Sales.SalesAmount, Contact.PhoneNo
FROM Customer, Sales, Contact
WHERE Customer.CustomerID = Sales.CustomerID
AND  Customer.CustomerID = Contact.CustomerID

This would return the following:

CustomerName	SalesAmount	PhoneNo
Jaz Lichy	$19.00	0123456
Jaz Lichy	$22.00	0123456
Ken Howe	$47.00	9876543

This is because only records with a CustomerID of 1 and 2 can be joined in all three tables. If, however, we wish to show all records in the Customer table and those records in the Sales and Contact tables whether they have a Customer table entry or not, then we must employ outer joins on these two tables as follows:

SELECT Customer.CustomerName, Sales.SalesAmount, Contact.PhoneNo
FROM (Customer LEFT JOIN Contact 
ON Customer.CustomerID = Contact.CustomerID) LEFT JOIN Sales 
ON Customer.CustomerID = Sales.CustomerID

This will return a larger recordset as follows:

CustomerName	SalesAmount	PhoneNo
Jaz Lichy	$19.00	0123456
Jaz Lichy	$22.00	0123456
Ken Howe	$47.00	9876543
Ken Jones		2460810
John Smith	$62.50
Joe Bloggs		0128910

As stated before this shows null fields, but highlights data omissions.

Ordering Data

Often the order in which data is retrieved is important and can be achieved with the order by clause. Here are a few examples:

SELECT * FROM Customer
ORDER BY Customer.CustomerName

This returns both CustomerID and CustomerName in descending alphabetical order determined by CustomerName

SELECT * FROM Customer
ORDER BY Customer.CustomerID

This returns both CustomerID and CustomerName in descending numeric order determined by CustomerID

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

This returns a recordset ordered alphabetically by CustomerName, but where multiple rows exist with the same CustomerName then these are then ordered numerically by the SalesAmount.

Summing and Grouping

Summing data returned by a query can be useful with data such as that in the Sales table or similar numerical data. If the Sales table shows hundreds or thousands of records for individual sales by several sales people, then using the sum and group by clauses totals up the sales for each sales person as shown in the query below:

SELECT Customer.CustomerName, SUM(Sales.SalesAmount) as SalesTotal
FROM Customer, Sales 
WHERE customer.CustomerID = Sales.CustomerID 
GROUP BY Customer.CustomerName
ORDER BY Customer.CustomerName

The group by clause is mandatory when summing data because it provides the information for query to understand what to sum around. For example if another field existed which gave the month of the sale then grouping by the customer and the month would return a subtotal for each customer for each month.

Search Queries

Where potentially vast amounts of data exist in a query resultset, then searching for similarities or patterns in the data is often helpful. E.g., providing the first few characters of someone's surname would reduce the size of resultset and is done as follows:

SELECT Customer.CustomerName
FROM Customer
WHERE Customer.CustomerName LIKE '*Smith'

returns all customers with the name Smith, irrespective of proceeding names. However, using:

WHERE Customer.CustomerName LIKE '*Smith*'

ensures all customers with Smith anywhere in their name, including double-barrel names, are returned.
For more specific searches, we can replace the * wildcard with a %, which is used to represent a single character rather multiple characters in the case of the * wildcard. Suppose we wish to return all phone numbers from the Contact table where we know the number has 7 digits, but want only those where the second digit is a 1 then the query would be:

SELECT Customer.CustomerName, Contact.PhoneNo
FROM Customer, Contact
WHERE Customer.CustomerID = Sales.CustomerID
AND Contact.PhoneNo LIKE '%1%%%%%'
ORDER BY Contact.PhoneNo

Other useful conditions are the less than or greater than clauses. The following query could be used to determine the sales made exceeding $1000, but by within specific group of customerIDs.

SELECT Customer.CustomerName, Sales.SalesAmount 
FROM Customer, Sales 
WHERE customer.CustomerID = Sales.CustomerID
AND Sales.SalesAmount > 1000
AND Sales.CustomerID < 10
ORDER BY Customer.CustomerName, Sales.SalesAmount

Counting Records

The ability to retrieve the number of records in a table, with or without where conditions is achieved using the count clause as follows.

SELECT COUNT(Customer.CustomerName)
FROM Customer

If the field being counted is not unique and has duplicate entries, then the distinct keyword should be used:

SELECT COUNT(DISTINCT Sales.CustomerID)
FROM Sales.

Hopefully this gives some insight into the potential of SQL and by combining these queries, you're able to retrieve the required data from tables. Also using query builders in database applications is an excellent way to learn more complex queries. Take a look at MS Access of MS SQL Server.

Return to the previous SQL article.