1

Conditions:

  1. 3 distinct pizzas, total price <=$80,

  2. Each pizza must be liked by at least 1 of customer (A/B/C). A,B,C are fixed requirements

  3. (A & B & C) must like at least 2 of the 3 selected pizzas

Expected Results: List with (Restaurant name, pizza1,pizza2,pizza3, Total cost)

Database schema: Customers(cname, area), Restaurants(rname, area), Pizzas(pizza), Sells(rname,pizza,price), Likes(cname, pizza)

Sells table

| rname  | Pizzas    | Price |
------------------------------
| rname1 | Hawaiian  | $10   |
| rname2 | Pepperoni | $20   |
| rname2 | Pizza3    | $20   |
| rname3 | Pizza4    | $20   |

Likes table

| cname  | Pizzas   
----------------------
| A      | Hawaiian  |
| A      | Pizza3    |
| A      | Pepperoni | 
| B      | Pizza3    | 
| B      | Hawaiian  |
| C      | Hawaiian  |
| D      | Pizza4    |

The 3 possible pizzas would be Hawaiian, Pizza3 and Pepperoni.

So A must like at least Hawaiian & Pizza3. B must like at least Pizza3 & Hawaiian while C must like at least 2/3 of the pizza which is Pepperoni and Hawaiian.

A & B & C need not like the same pizza. But each pizza must be like by at least 1 customer.

SQL Query:

SELECT s1.rname, s1.pizza, s2.pizza, s3.pizza, (s1.price+s2.price+s3.price)
FROM Sells s1 join
     Sells s2 join
     Sells s3
ON   s1.rname = s2.rname = s3. rname 
AND  s1.price < s2.price < s3.price 
WHERE s1.pizza < s2.pizza < s3.pizza

Issue: I still need to add the likes table into the query to check

  • Each pizza must be liked by at least 1 of (A/B/C).

  • (A & B & C) must like at least 2 of the 3 pizzas

Reference for database schema: http://sqlfiddle.com/#!9/dceae9/1

1 Answers1

0

I've started to look at the question cause you had a postgresql tag on it. In PostgreSQL I'd try to write a query which joins sells and likes tables and then aggregates them into arrays, so then you can unnest arrays into all possible combinations.

I don't know MySQL sql dialect very well, so here's some straightforward query (notice I had to change your data a bit cause you didn't have any combinations which fullfil all the requirements):

select
    s1.rname,
    s1.pizza, s2.pizza, s3.pizza,
    l1.cname, l2.cname, l3.cname,
    (s1.price+s2.price+s3.price)
from Sells as s1
    inner join Sells as s2 on
        s2.rname = s1.rname
    inner join Sells as s3 on
        s3.rname = s2.rname
    left join likes as l1 on
        l1.pizza = s1.pizza and
        l1.cname = 'alice'
    left join likes as l2 on
        l2.pizza = s2.pizza and
        l2.cname = 'bob'
    left join likes as l3 on
        l3.pizza = s3.pizza and
        l3.cname = 'james'
where
    s1.pizza <> s2.pizza and
    s3.pizza <> s2.pizza and
    s3.pizza <> s1.pizza and
    (s1.price+s2.price+s3.price) <= 80 and
    (
        l1.cname is not null and l2.cname is not null or
        l2.cname is not null and l3.cname is not null or
        l3.cname is not null and l1.cname is not null
    )

sql fiddle example

Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
  • Great help! Just needed to clarify the last part. It is to test whether (A & B & C) must like at least 2 of the 3 pizzas right? –  Feb 24 '18 at 12:21
  • 1
    yes, it's last part. If you're more familiar with MySQL, you could some nicer query, see https://stackoverflow.com/questions/18193365/count-of-non-null-columns-in-each-row/18193745#18193745 (this one is for MS SQL, so I'm not sure if it will work in MySQL) – Roman Pekar Feb 24 '18 at 12:24
  • Hi, i've been trying to out the code in sql fiddle. Was wondering how do i make my rname distinct? Because i have too many duplicates and i'm trying to add on another condition called s1.price –  Feb 24 '18 at 16:21
  • Or should i ask it in another question? :) –  Feb 24 '18 at 16:22
  • No it's a valid point, I take a look a bit later. Now with proper data it's much easier. So it doesn't matter for you who like what as long as you have >= 2 likes? – Roman Pekar Feb 24 '18 at 16:43
  • Yeap. The conditions above remains the same. Just that i added in a new condition. –  Feb 25 '18 at 01:22