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.


RNum | RestaurantName | HQLocation

and the orders table:


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:

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

and here is another:

FROM Restaurants AS R
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