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如果Products表或Employees表中不存在对应记录,则用LEFT JOIN
一定要考虑好用什么连接: 1.如果Products表<==>Employees表中存在对应记录,则用inner JOIN 2.如果Products表<==>Employees表中不存在对应记录,则用left JOINSELECT 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
select b.ProductName,c.LastName from Orders a left join Products b on a.ProductID=b.ProductID left join Employees c on a.EmployeeID=c.EmployeeID
奇怪楼主Orders表中没有ProductID字段
楼主,你的Orders表时数据库自带的表吗Orders表中没有字段ProductID奇怪
select P.ProductName,E.LastName from Orders O,Products P,Employees E where O.ProcuctID=P.ProductID and O.EmployeeID=E.EmployeeID如果Products表或Employees表中不存在对应记录,需要在程序中对NULL值进行处理...
to everyone: 谢谢大家! to ysycrazy(风中狂): 你的那位“丢丢”好友不是我,因为我QQ里的名字不是这个:)
查询条件不是太明确, (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]是 一样的
FROM Orders o INNER JOIN Products p ON o.ProductID = p.ProductID
INNER JOIN Employees e ON o.EmployeeID = e.EmployeeID如果Products表或Employees表中不存在对应记录,则用LEFT JOIN
1.如果Products表<==>Employees表中存在对应记录,则用inner JOIN
2.如果Products表<==>Employees表中不存在对应记录,则用left JOINSELECT 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
left join Products b on a.ProductID=b.ProductID
left join Employees c on a.EmployeeID=c.EmployeeID
from Orders O,Products P,Employees E
where O.ProcuctID=P.ProductID and O.EmployeeID=E.EmployeeID如果Products表或Employees表中不存在对应记录,需要在程序中对NULL值进行处理...
谢谢大家!
to ysycrazy(风中狂):
你的那位“丢丢”好友不是我,因为我QQ里的名字不是这个:)
--只有一种情况,那就是匹配
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]是
一样的
(2) 中应该是使用 INNER JOIN +OUTER JOIN 来实现嘿嘿,希望可以帮上忙
!