Find at least 2 customers in the same area who like at least one pizza sold in SQL

Question

Tables in SQL Query

• Likes (cname, pizza)

• Customers (cname, area)

• Restaurants (rname, area)

• Sells (rname, pizza, price)

Refer to: sqlfiddle.com/#!9/5be81e/1 (Contains code and database schema)

Expected Result : A list of restaurants that meet the conditions below.

Conditions

• At least 2 customers, at least one pizza sold in a restaurant. (They don’t necessarily look like the same pizza that the restaurant sells, the client and the restaurant should not be in the same area).

• The restaurant must sell at least 3 pizzas

• At least one pizza sold in a restaurant should be cheaper than $ 20

My SQL code:

SELECT r.rname
FROM restaurants r
WHERE EXISTS(SELECT 1
         FROM sells s 
         INNER JOIN likes l ON l.pizza = s.pizza
         GROUP BY c.area
         HAVING COUNT(l.pizza) >= 2
         WHERE s.rname = r.rname 
         AND s.pizza >=3 
         AND s.price <20
         )

SQL, WHERE EXIST. 1.

: . , .

Answer 1

- , :

SELECT r.rname
FROM restaurants r
JOIN sells s ON s.rname = r.rname
LEFT JOIN likes l on l.pizza = s.pizza
LEFT JOIN customers c
  ON  c.area  = r.area
  AND c.cname = l.cname
GROUP BY r.rname
HAVING 1
   AND COUNT(DISTINCT c.cname) >= 2 -- cond. 1
   AND COUNT(DISTINCT s.pizza) >= 3 -- cond. 2
   AND MIN(s.price) < 20            -- cond. 3
ORDER BY r.rname

, , , . 1 2 3. :

SELECT *
FROM (
    SELECT r.rname
    FROM restaurants r
    JOIN sells s ON s.rname = r.rname
    JOIN likes l on l.pizza = s.pizza
    JOIN customers c
      ON  c.area  = r.area
      AND c.cname = l.cname
    GROUP BY r.rname
    HAVING COUNT(DISTINCT c.cname) >= 2
) t1
NATURAL JOIN (
    SELECT s.rname
    FROM sells s
    GROUP BY s.rname
    HAVING MIN(s.price) < 20 
       AND COUNT(*) >= 3
) t2

, , EXISTS JOIN.

SELECT r.rname
FROM restaurants r
JOIN sells s ON s.rname = r.rname
JOIN likes l on l.pizza = s.pizza
JOIN customers c
  ON  c.area  = r.area
  AND c.cname = l.cname
GROUP BY r.rname
HAVING COUNT(DISTINCT c.cname) >= 2
   AND EXISTS (
     SELECT s.rname
     FROM sells s
     WHERE s.rname = r.rname
     HAVING MIN(s.price) < 20 
        AND COUNT(*) >= 3
)

, , , , c.area = r.area. restaurants customers. , :

SELECT s.rname
FROM sells s
LEFT JOIN likes l on l.pizza = s.pizza
GROUP BY s.rname
HAVING COUNT(DISTINCT l.cname) >= 2
   AND COUNT(DISTINCT s.pizza) >= 3
   AND MIN(s.price) < 20

Answer 2

1 GROUP BY restaurants.rname HAVING COUNT(distinct Customers.cname) > 1:

    SELECT restaurants.rname
    FROM restaurants
    inner join
    customers
    on restaurants.area = customers.area
    inner join
    likes
    on customers.cname = likes.cname
    inner join
    sells
    on likes.pizza = sells.pizza and restaurants.rname = sells.rname
    where
    restaurants.rname in 
    (select r.rname from 
    restaurants r
    inner join
    sells s
    on r.rname = s.rname
    group by r.rname
    having count(  s.pizza) >= 3  
    and min(s.price) < 20)

    group by restaurants.rname
    having count(distinct customers.cname) > 1

Answer 3

WHERE EXISTS, 3 EXISTS, , . , .

WHERE EXSISTS, 3 EXISTS, :

SELECT r.rname
FROM restaurants r
WHERE EXISTS(SELECT 1
             FROM likes l
             INNER JOIN customers c ON c.cname = l.cname
             INNER JOIN sells s ON s.pizza = l.pizza
             WHERE s.rname = r.rname
             GROUP BY s.rname, l.cname
             HAVING COUNT(l.pizza) >= 2
             )
  AND EXISTS(SELECT 1
             FROM sells s
             WHERE s.rname = r.rname
             GROUP BY s.rname
             HAVING COUNT(s.pizza) >= 3
             )
  AND EXISTS(SELECT 1
             FROM sells s
             WHERE s.rname = r.rname
               AND s.price < 20
             )

Each of the operators EXISTSmeets your conditions. This makes it more intuitive for you, and you do not need to worry about filling it all with just one EXISTS.