[sql] Subqueries with exists

[CJPowell27]

Hey guys so I was given the below table layout as a practice problem. My professor wants me to use a nested query with the exists predicate to print the customer name and amount they paid, but only for the customer that paid using credit or check AND bought milk or coke. Logically I am aware that the command should be printing out Grace Johnson for the name and 11.75 as the amount. I also understand that the exists predicate should only be used to return a true or false value, so where would the correct place to use that keyword? I got it working in another way without the exists keyword using 

select * from customer where customerID in (select customerID from orders where ordertype='Credit' and orderID in (select orderID from items where items='Cookies' or items='Coke'));

Table Layout:

[shadow_ray]

You could join customer and order tables and use the items table as a filter.

select Customer.CustomerName, sum(Orders.Amout)
from Orders
join Customers on Orders.CustomerID = Customer.CustomerID
where exists( select * from Items where Items.OrderId = Orders.OrderId and (Items.Items = 'Milk' or Items.Items = 'Coke'))
and  (Orders.OrderType = 'Credit' or Orders.Ordertype = 'Check') 
group by Customer.CustomerID

I think this should work...

[CJPowell27]

3 hours ago, shadow_ray said:

You could join customer and order tables and use the items table as a filter.

select Customer.CustomerName, sum(Orders.Amout)
from Orders
join Customers on Orders.CustomerID = Customer.CustomerID
where exists( select * from Items where Items.OrderId = Orders.OrderId and (Items.Items = 'Milk' or Items.Items = 'Coke'))
and  (Orders.OrderType = 'Credit' or Orders.Ordertype = 'Check') 
group by Customer.CustomerID

I think this should work...

that makes so much sense, basically all the stuff in the quotes filters it so that it would only return Grace Johnson (because given criteria) as true then returns that to the outer query right? It worked

[shadow_ray]

9 minutes ago, CJPowell27 said:

that makes so much sense, basically all the stuff in the quotes filters it so that it would only return Grace Johnson (because given criteria) as true then returns that to the outer query right? It worked

 

Yep, the exists operator returns true if the subquery returns one or more records.

The sum() function and group by Customer.CustomerID statement is there to sum up all orders from the same customer.