How to list orders in sql query?

Question

I'm trying to get a list of my orders (employee name, order date, customer) using the northwind database:

northwind database diagram

Can you see what is wrong with my query?

select 
    e.FirstName as Name, e.LastName as Lastname, 
    o.OrderDate as Date, s.CompanyName as Customer
from 
    Emplyees e 
join 
    Orders o on e.EmployeeID = o.EmployeeID

Answer 1

You have a typo in from Emplyees, where Employees is missing the o. Also, as commented by @GordonLinoff, there is an issue with s.CompanyName, as there is no table alias called s in your query.

By looking at your database schema, I assume that you are looking for the CompanyName field that comes from table Customers, as you aliased that column Customer (could as well be Shippers.CompanyName, but looks less likely).

If so, you want to add another JOIN in your query to include table Customers (aliased as c) :

select 
    e.FirstName, 
    e.LastName,
    o.OrderDate as Date, 
    c.CompanyName as CustomerCompany
from
    Employees e 
    join Orders    o on o.EmployeeID = e.EmployeeID
    join Customers c on c.CustomerID = o.CustomerID

NB : aliasing an output column with the same name is redundant, I removed that.