Comparing the location of values in different tables

Question

New to SQL. Hope to find distinct pizza sold by restaurants that are located in the same area as bob (customer).

Database schema:

Customers(cname, area)

Restaurants(rname, area)

Pizzas(pizza)

Sells(rname,pizza,price)

Likes(cname, pizza)

   SELECT distnct pizza
   FROM Sells
   WHERE rname in (
      SELECT rname
      FROM Restaurants
      WHERE area..... 
   -- This is the part where i don't know how to continue. 
   -- How do i write it so that i compare the location of the restaurant with bob's location? 
   );

score 0 · Accepted Answer · answered Feb 15 '18 at 15:01

Seems to me that a combination of an INNER JOIN and EXISTS should do:

SELECT DISTINCT(s.pizza)
FROM dbo.Sells s
INNER JOIN dbo.Restaurants r
    ON s.rname = r.rname
WHERE EXISTS(SELECT 1 FROM dbo.Customers
             WHERE cname = 'bob'
             AND area = r.area) 
;

Comparing the location of values in different tables

1 Answers1