This is a follow-up to this post about a repeatable set of steps you can take to build simple SQL SELECT queries. If you’re ready to give it a try, this is the place!

To demonstrate building a query, I’m going to use SSMS and Microsoft’s Northwind sample database.

Here’s the assignment e-mail they sent you:
“Our internal auditing team needs to see a list of products for orders from our US customers in May 1997. To make it easy to read, give us each customer’s orders together, along with the exact dates the products were ordered, in sorted order, too.”

1. State the question: “What products did each of our US customers order in May of 1997?”

2. Build a list of tables we need to answer the question:

–CUSTOMERS: Contains the company name and Country

–ORDERS: Contains the Order Date

–PRODUCTS: Contains the Product Name

3. Look at the tables you’ve identified and find out how they’re related

It turns out, in this case, that even though we don’t need to display any results from the Order Details table, we need it to be part of the query to make our “join plumbing” work: Order Details is related to both the Orders and Products table, so we’ll add that as our last table to be part of our eventual SQL.

4. Build the query!

FIRST: A simple SELECT with one table

--First table
SELECT * 
FROM Customers;

I like starting with SELECT * as a placeholder (Hopefully you’re not doing dev on a live production DB, but if so, be careful about running a lot of these tests so that they don’t interfere with mission-critical SQL from other connections). Remember, at any point from here on, you should be able to run the query you have, to test your SQL syntax’s correctness, and to see what “the answer to your question” is so far.

SECOND: Add JOINs one at a time

--Add joins one at a time: Orders..
SELECT *
FROM Customers as C
INNER JOIN Orders as O
on C.customerid=O.customerid;


---Order Details..
SELECT *
FROM Customers as C
INNER JOIN Orders as O
on C.customerid=O.customerid
INNER JOIN [Order Details] as OD
on OD.orderid=O.orderid;


--Products...
SELECT *
FROM Customers as C
INNER JOIN Orders as O
on C.customerid=O.customerid
INNER JOIN [Order Details] as OD
on OD.orderid=O.orderid
INNER JOIN Products as P
on P.ProductID=OD.ProductID;

For queries of this type, you should have one fewer join that the number of tables in the query: Two tables, one join. Four tables, 3 joins, etc. (Since each join works to unite EXACTLY 2 tables).

THIRD: Choose your columns and create any custom outputs

--Choose the columns you want to display
--(Here using the CONVERT function to manage
--  how our DATEs display)
SELECT C.CompanyName
, CONVERT(varchar(20),O.OrderDate,107) as OrderDate
, P.ProductName
FROM Customers as C
INNER JOIN Orders as O
on C.customerid=O.customerid
INNER JOIN [Order Details] as OD
on OD.orderid=O.orderid
INNER JOIN Products as P
on P.ProductID=OD.ProductID;

We call this step “filter vertically” because we’re choosing columns. Look at your question for what needs to be displayed in the results. I chose to use the SQL Convert function to manage the display of dates, since I didn’t want to see the Time portion.

FOURTH: Use your WHERE clause to filter by row

--Filter by rows
SELECT C.CompanyName
, CONVERT(varchar(20),O.OrderDate,107) as OrderDate
, P.ProductName
FROM Customers as C
INNER JOIN Orders as O
on C.customerid=O.customerid
INNER JOIN [Order Details] as OD
on OD.orderid=O.orderid
INNER JOIN Products as P
on P.ProductID=OD.ProductID
WHERE C.Country='USA' and MONTH(O.OrderDate)=5 
   and YEAR(O.OrderDate)=1997;

Our WHERE clause tells our query to include only rows where the Customer’s Country is USA, then uses the MONTH and YEAR functions to also only include rows where the Order was placed in May 1997 (This portion of the condition could also have been accomplished using a BETWEEN operator to span May 1 to May 31, but if you don’t want to deal with the hours/minutes/seconds parts of DateTime values, this approach just seems easier)

FIFTH: Add an ORDER BY expression to do sorting

--Sort the results
SELECT C.CompanyName
, CONVERT(varchar(20),O.OrderDate,107) as OrderDate
, P.ProductName
FROM Customers as C
INNER JOIN Orders as O
on C.customerid=O.customerid
INNER JOIN [Order Details] as OD
on OD.orderid=O.orderid
INNER JOIN Products as P
on P.ProductID=OD.ProductID
WHERE C.Country='USA' and MONTH(O.OrderDate)=5 and YEAR(O.OrderDate)=1997
ORDER BY C.CompanyName ASC, O.OrderDate ASC;

Remember our question required results to show first in alpha order by Customer, then for each customer by order date.

If you want to check your results, here’s what I got when we ran this query, so while our sorting seems unnecessary in this case, understand that with more and/or different results, it still matters.

Until next time, happy coding!

Print 🖨