Using JOIN statement with a WHERE This is probably a basic question, but I'm trying to build an SQL query for an access database that returns orders from a certain customer and I want to cross reference the customerID with the customer name from the customers table.

This is the query that I'm using

SELECT OrderTable.OrderNumber, OrderTable.CustomerID, OrderTable.OrderDate 
FROM [OrderTable] INNER JOIN [CustomerTable] 
    ON CustomerTable.CustomerID = OrderTable.CustomerID 
WHERE OrderTable.CustomerID = 1


I don't get an error and it returns the correct records (all orders w/ customer ID = 1), however it does not return the customer name...it just returns the customerID for each order (always 1 as expected).

What am I doing incorrectly?

Thank you!
You are specifically asking for the OrderNumber, CustomerId and OrderDate columns.  If you want the customers name you have to include that in your field list.

SELECT OrderTable.OrderNumber, OrderTable.CustomerID, OrderTable.OrderDate,
     CustomerTable.Name
FROM [OrderTable] INNER JOIN [CustomerTable] 
    ON CustomerTable.CustomerID = OrderTable.CustomerID 
WHERE OrderTable.CustomerID = 1


A basic SELECT statement looks something like this:

SELECT FieldList
FROM TableOne
INNER JOIN TableTwo
    ON JoinCondition between TableOne and TableTwo
WHERE [Conditions that restrict returned rows]


You will only be returned those columns that you explicitly list in the FieldList unless you use a * (returns all rows) or TableName.* (returns all rows for TableName).  

Note: It is recommended that you list out the fields you want back explicitly for a number of reasons including:


Performance : Pulling one (or more) columns that you actually need requires less data transferred than pulling all of them.
Code stability : If you add a column to the table you will get back columns in your query you may not want and could break your code.