三个表之间的JOIN QUERY [英] JOIN QUERY between three tables

问题描述

我有三个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

推荐答案

首先，在我的Northwind数据库副本中，直接从MSDN下载，表格被称为员工 s ，订单 s 和客户取值 。确切来说很重要。



可以使用JOIN查询表格 - 此CodeProject文章解释了各种类型的连接 SQL连接的可视化表示 [ ^ ]



因为你想要限制返回的信息 - 所有拥有客户的员工暗示不显示不拥有客户的员工， INNER JOIN 是合适的 - 请参阅文章以了解原因。
您实际上不需要在三个表中加入，因为如果您查看表格的创建脚本，您将看到

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

（在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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！