Firstly, in my copy of the Northwind database, downloaded directly from MSDN, the tables are called
Employees
,
Orders
and
Customers
. It is important to be precise.
The tables can be queried using JOINs - this CodeProject article explains the various types of join
Visual Representation of SQL Joins[
^]
Because you are trying to
limit the information returned - "all employees that have customers" implies "do not show employees that do
not have customers", an
INNER JOIN
would be appropriate - refer to the article to see why.
You don't actually need to join across the three tables because if you look at the Create script for the table Orders you will see
...
[CustomerID] [nchar](5) NULL,
...
ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD CONSTRAINT [FK_Orders_Customers] FOREIGN KEY([CustomerID])
REFERENCES [dbo].[Customers] ([CustomerID])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Customers]
GO
(In SQL Server Management Studio, if you right-click on a table name in the Object Explorer window, select "Script table as" then "CREATE to" then "New Query Editor Window" you can get to the information pasted above).
That basically means that if you do include a value for
CustomerID
on the
Orders
table then it
must exist as
CustomerID
on the
Customers
table, otherwise that column will contain
NULL
.
So, to just list the Employees details that appear on the Orders table:
Select E.LastName, E.FirstName, E.Title
FROM Orders O
INNER JOIN Employees E ON O.EmployeeID = E.EmployeeID
WHERE CustomerID IS NOT NULL
That happens to be exactly the same as including
Customers
on another
INNER JOIN
i.e.
Select E.LastName, E.FirstName, E.Title
FROM Orders O
INNER JOIN Employees E ON O.EmployeeID = E.EmployeeID
INNER JOIN Customers C ON O.CustomerID = C.CustomerID
Again, if you look at the images on that article it will become clear why.
Translating that to MVC, Linq or whatever else you are trying to do is left as an exercise for you, as you have not provided enough information (nor effort) for me to help you with that.