CODEWITHSUNDEEP
sqlms-access
user8620505
user8620505

Reputation:

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?

Upvotes: 0

Views: 66

Answers (4)

sheraz
sheraz

Reputation: 464

Try below code. 

select distinct r.RestaurantName from Restaurants as r
inner join Orders as o ON o.RNum = r.RNum
where o.CNum='c200';

Upvotes: 0

June7
June7

Reputation: 21370

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.

Upvotes: 1

londy_2000
londy_2000

Reputation: 1

May this sql will work as below:

SELECT DISTINCT RestaurantName 
FROM Restaurants 
WHERE RNum IN 
(
    SELECT RNum 
    FROM Orders 
    WHERE CNum = "C200"
)

Upvotes: 0

YK S
YK S

Reputation: 136

A tiny improvement from June7's answer: 

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

Upvotes: 1

Related Questions