I have 3 tables:

Size (Columns: Id, Name)

Topping (Columns: Id, Name)

Pizza (Columns: Id, SizeId, ToppingId)

Size and Topping are only connected through the Pizza table.

How can I get every possible combination of Size Topping that isn't already in the Pizza table?

ex:

Sizes = {Id: 1, Medium}, {Id: 2, Name: Large}, {Id: 3, Name: X-Large}

Toppings = {Id: 1, Name: Pepperoni}, {Id: 2, Name: Ham}

Pizzas = {Id: 1, SizeId: 2, ToppingId: 1}, {Id: 2, SizeId: 3, ToppingId: 2}

I would expect the following output:

note how Large Pepperoni and X-Large Ham don't show up as they are already on the Pizzas table

CodePudding user response：

You should use EXCEPT to get the difference between the following two sets:

• all combinations of size and toppings
• records present in pizza table

Working on ids should make joins more efficient. Once you get your final table with ids, you can get back infos from the sizes and toppings table.

WITH cte AS (
    SELECT * 
    FROM       (SELECT Id AS sizeId    FROM sizes   ) s
    CROSS JOIN (SELECT Id AS toppingId FROM toppings) t

    EXCEPT 

    SELECT sizeId, toppingId FROM pizzas p
)
SELECT s.Name AS size, t.Name AS topping
FROM       cte
INNER JOIN sizes s    ON cte.sizeId = s.Id
INNER JOIN toppings t ON cte.toppingId = t.Id

CodePudding user response：

you can use a cross join to get all the possible size and topping ids combinations, and use the EXCEPT operator to 'subtract' that from the combinations present in the pizza table. then you can simply join the three tables on size.id and topping.id to get names.

select s.name, t.name
from (
  select size.id sid, topping.id tid
  from size cross join topping
  EXCEPT
  select pizza.sizeid sid, pizza.toppingid tid
  from pizza
) AA, size s, topping t
where AA.sid=s.id AND AA.tid=t.id