mzaccaro89 mzaccaro89 - 3 years ago 113
SQL Question

Another SQL Query

this is a homework question regarding SQL. The two tables being used are as follows, the data in them is irrelevant.

Restaurants

RNum | RestaurantName | HQLocation


and the orders table:

Orders

CNum | DNum | RNum | Quant | Price


The issue I'm having is that I cannot figure out how to specify Restaurants that have their only orders from customer C200. The query is worded as such:

"Name the restaurants whose only order(s) came from customer C200."

I have tried to solve this a bunch of different ways and will post two that I thought will work, though neither did as the answer table should only involve one record.

Here is my first try:

SELECT DISTINCT R.RestaurantName
FROM Orders AS O, Restaurants AS R
WHERE (O.RNum = R.RNum)
AND (O.CNum = 'C200')


and here is another:

SELECT DISTINCT R.RestaurantName
FROM Restaurants AS R
WHERE EXISTS
(SELECT *
FROM Orders AS O
WHERE (O.RNum = R.RNum)
AND (O.Rnum = 'C200'))


I'm having a lot of trouble excluding all the other possibilities and all of my queries keep returning more than one record. Is there a way using nested queries (no joins) to do this?

Answer Source

Give this a try:

SELECT * FROM Restaurants WHERE RNum IN (SELECT RNum FROM Orders GROUP BY RNum HAVING Sum(IIf([CNum]<>'C200',1,0))=0);

Using data from your other thread, 3 restaurants meet the criteria.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download