I'm trying to create a view of some columns from 3 different tables. One of the columns 'OrderNumber' is in 2 of the tables so I'm trying to do a UNION for them, but because I've made a subquery it returns an 1242 error and won't return more than 1 row. I just want to know how I can rewrite this query so that there are no subqueries, or is there someway to bypass it. Or perhaps I need to write multiple queries? Though I'd prefer to keep it to the one query, thanks.
CREATE VIEW CustOrderItems AS
QuantityOrdered * QuotedPrice as ItemTotal
FROM Customers JOIN Orders JOIN Order_Details;
Substitute whatever your customer 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 = ?