1

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!

  • I do not see the CustomerName being selected from the CustomerTable. The join apparently works, but you need to add CustomerTable.CustomerName to the select statement to get that datum. – RLF Mar 10 '16 at 21:23
2

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.
1

taking a guess here

select ... , [CustomerTable].[name]

SELECT OrderTable.OrderNumber, OrderTable.CustomerID, OrderTable.OrderDate 
     , [CustomerTable].[name]
  FROM [OrderTable] 
  JOIN [CustomerTable] 
         ON CustomerTable.CustomerID = OrderTable.CustomerID 
        AND                            OrderTable.CustomerID = 1
0

Change your query select,

from: SELECT OrderTable.OrderNumber, OrderTable.CustomerID, ....

to: SELECT OrderTable.OrderNumber, CustomerTable.CustomerName, .... (or equivalent)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.