问题描述
我有三个Northwind数据库表，Employee（employeeID）1- * Order（Orderid，customerid，employeeid）* - 1个Customer（customerID）。 1员工有多个订单，1个客户有多个订单，因此它是1 * * 1的关系，中间有一个订单表。那么我如何在一个mvc视图中提取和显示所有有客户的员工？
我尝试过的事情：
i刚刚解释了上面的问题
I have three tables of Northwind database , Employee(employeeID) 1-* Order(Orderid,customerid,employeeid)*-1 Customer(customerID) . 1 Employee has many Orders and 1 customer have many orders , so it is 1* *1 relationship , with an order table in the middle. So how do i extract and display in a mvc view ALL EMPLOYEES THAT HAVE CUSTOMERS ?
What I have tried:
i just explained the problem above
推荐答案
...
[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（在SQL Server Management Studio中，如果你正确的话 - 在对象资源管理器窗口中单击一个表名，选择脚本表为，然后选择创建到，然后选择新建查询编辑器窗口，您可以获得上面粘贴的信息）。
这基本上意味着如果你在 Orders 表中包含 CustomerID 的值那么必须在 Customers 表中以 CustomerID 的形式存在，否则该列将包含 NULL 。
因此，只需列出Orders表中显示的Employees详细信息：
(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这恰好与包含 Customers 在另一个 INNER JOIN 即
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再次，如果你看一下那篇文章中的图片，就会明白为什么。
将它翻译成MVC，Linq或者无论你想做什么，都留给你作为练习，因为你没有提供足够的信息（也没有努力）让我帮助你。
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.
这篇关于三个表之间的JOIN QUERY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！