So basically, i need to get the name and addresses of everyone that's ordered a product (or number of products) that totals over ?10, this means I cant just do a WHERE p.ProductPrice > 10 as p.ProductPrice returns multiple values, and i cannot do SUM as it's not allowed in the syntax.
Here's what i have so far:
SELECT c.CustomerName, SUM( p.ProductPrice ) AS TotalShipping
FROM Customer c
JOIN `Order` o ON o.CustomerNo = c.CustomerNo
JOIN OrderedProduct op ON op.OrderNo = o.OrderNo
JOIN Product p ON p.ProductNo = op.ProductNo
GROUP BY o.OrderNo
This works find for showing the Customer Name and the Total of all the shipping items, however, i need to get it to only show the ones over ?10, and i cannot do WHERE TotalShipping > 10 - and i'm pretty sure my nested sub-query didn't work properly either.
Is there any ways around this, it's probably quite simple i'm just can't think of it.
Thanks for the help in advance!
p.s. This is one of many questions on my paper, so i'm not asking for help where i could learn it - i've tried many different ways and consider this a last option for educational reasons - i just hope someone can help!
Question
PRSBOY
Hey there, I have been given a long list of "queries" that I need to write out, i've got them all done apart from one which really has me stumped.
I'll show you the question and then show you what I have and the problems i'm having. Hopefully someone can help.
Question:
List the names and addresses of the customers who ordered products worth ?10 or more.>
Tables:Order(OrderNo, OrderDate, CustomerNo,)
OrderedProduct(OrderNo, ProductNo)
Product(ProductNo, ProductDescription, ProductPrice, No_in_stock, Reorder_level)
Customer(CustomerNo, CustomerName, CustomerAddress) >
So basically, i need to get the name and addresses of everyone that's ordered a product (or number of products) that totals over ?10, this means I cant just do a WHERE p.ProductPrice > 10 as p.ProductPrice returns multiple values, and i cannot do SUM as it's not allowed in the syntax.
Here's what i have so far:
This works find for showing the Customer Name and the Total of all the shipping items, however, i need to get it to only show the ones over ?10, and i cannot do WHERE TotalShipping > 10 - and i'm pretty sure my nested sub-query didn't work properly either.
Is there any ways around this, it's probably quite simple i'm just can't think of it.
Thanks for the help in advance!
p.s. This is one of many questions on my paper, so i'm not asking for help where i could learn it - i've tried many different ways and consider this a last option for educational reasons - i just hope someone can help!
Link to comment
Share on other sites
7 answers to this question
Recommended Posts