SELECT Orders.*, p.ProductName, e.LastName
FROM Orders o INNER JOIN Products p ON o.ProductID = p.ProductID
INNER JOIN Employees e ON o.EmployeeID = e.EmployeeID
(1)如果Products表<==>Employees表中存在对应记录,则用inner JOIN
--只有一种情况,那就是匹配
SELECT p.productName,e.LastName
FROM products p INNER JOIN orders o ON p.productID=o.productID
INNER JOIN employee e ON e.EmployeeID=o.EmployeeID
(2)如果Products表<==>Employees表中不存在对应记录,则用inner JOIN
a.取得 所有的定单中产品的名称及其相对应的employee.LastName
SELECT p.productName,isnull(e.LastName,'没有员工lastName')
FROM products p LEFT OUTER JOIN orders o ON p.productID=o.productID
INNER JOIN employee e ON
e.EmployeeID=o.EmployeeID
b.取得 所有的工作人员的 lastname 和其响应的定单 产品名称
SELECT ISNULL(p.productName,'该员工没有定单'),e.LastName
FROM products p INNER JOIN orders o ON p.productID=o.productID
LEFT OUTER JOIN employee e ON e.EmployeeID=o.EmployeeID
c.取得 所有匹配了员工 和 定单 的 lastname和 productName
同 (1)[如果Products表<==>Employees表中存在对应记录,则用inner JOIN]是
一样的
SELECT Orders.*, p.ProductName, e.LastName
FROM Orders o INNER JOIN Products p ON o.ProductID = p.ProductID
INNER JOIN Employees e ON o.EmployeeID = e.EmployeeID