MySQL 错误代码 1242

Question

我正在尝试创建 3 个不同表中某些列的 View 。其中一列“OrderNumber”位于其中 2 个表中，因此我尝试为它们执行 UNION，但因为我创建了一个子查询，所以它返回 1242 错误，并且不会返回超过 1 行。我只是想知道如何重写这个查询，以便没有子查询，或者有什么办法可以绕过它。或者也许我需要编写多个查询？虽然我更愿意将其保留为一个查询，谢谢。

CREATE VIEW CustOrderItems AS
SELECT CustFirstName,
       CustLastName,
       (SELECT OrderNumber
       FROM Orders
       UNION
       SELECT OrderNumber
       FROM Order_Details)
       OrderDate,
       ShipDate,
       QuantityOrdered * QuotedPrice as ItemTotal
FROM Customers JOIN Orders JOIN Order_Details;

Answer 1

替换您的客户 ID

drop view if exists custorders;
create view custorders as
SELECT     c.CustFirstName,
           c.CustLastName,
           o.OrderNumber order_ordernumber,
           od.OrderNumber orderdetails_ordernumber,
           o.OrderDate,
           o.ShipDate,
           od.QuantityOrdered * od.QuotedPrice as ItemTotal
FROM Customers c
JOIN Orders o on c.id = o.cust_id
JOIN Order_Details od on o.ordernumber = od.ordernumber
where c.id = ?