CODEWITHSUNDEEP
sqlsql-server
Mike
Mike

Reputation: 11

Where clause with multiple values

I am new to SQL and I am using MS SQL Server Management Studio 2014. I have 3 tables called Pizza, Pizza_Topping and Topping. I want to list the pizzas that have TOPPING1 and TOPPING2 as toppings. This is what I have came up with,

select  Pizza.pizzaID, Pizza.pizzaName, Topping.toppingName

from Pizza left join Pizza_Topping on
Pizza.pizzaID = Pizza_Topping.pizzaID

left join Topping on
Topping.toppingID = Pizza_Topping.toppingID

where Topping.toppingName in ('topping1', 'topping2')

and this gives me

pizzaID    pizzaName        toppingName
-------- ---------------- --------------
PZ002   |   PIZZA1       |    TOPPING1
PZ002   |   PIZZA1       |    TOPPING2
PZ010   |   PIZZA5       |    TOPPING1
PZ010   |   PIZZA5       |    TOPPING2
PZ011   |   PIZZA6       |    TOPPING1
PZ012   |   PIZZA7       |    TOPPING2

I only need first four rows because last two pizzas only have one of the toppings not both. I have tried this as well,

GROUP BY Pizza.pizzaID,Pizza.pizzaName, Topping.toppingName HAVING COUNT(toppingName) >= 2

but it didn't give the expected result. that line can be used if I am going to display only pizzaID and pizzaName but i want to display toppingName as well.

to be more clear, this what I am expecting

pizzaID    pizzaName        toppingName
-------- ---------------- --------------
PZ002   |   PIZZA1       |    TOPPING1
PZ002   |   PIZZA1       |    TOPPING2
PZ010   |   PIZZA5       |    TOPPING1
PZ010   |   PIZZA5       |    TOPPING2

Please tell me how to achieve this result. Thanks

Upvotes: 0

Views: 5028

Answers (6)

yuvraj
yuvraj

Reputation: 197

You can solve this problem by using group by statement restricted to Pizza Id. Please refer below query.

   select  Pizza.pizzaID, Pizza.pizzaName, Pizza_Toppings.Remark
   from Pizza  join Pizza_Toppings on
   Pizza.pizzaID = Pizza_Toppings.pizzaID
   join Toppings on
   Toppings.toppingID = Pizza_Toppings.toppingID
   where Pizza.PizzaId in (
   select Pizza.PizzaID
   from Pizza  join Pizza_Toppings on
   Pizza.pizzaID = Pizza_Toppings.pizzaID
   join Toppings on
   Toppings.toppingID = Pizza_Toppings.toppingID
   where Toppings.TopingName in ('topping1', 'topping2')
   group by pizza.PizzaId 
   having count(Pizza.PizzaId ) > 1)

Please let me know if this does not work for you.

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20794

You have to join to the pizza_topping and topping tables twice.

select yourfields
from pizza join pizza_topping pt1 on pizza.pizzaID = pt1.pizzaID
join topping t1 on pt1.toppingID = t1.toppingId

join pizza_topping pt2 on pizza.pizzaID = pt2.pizzaID
join topping t2 on pt2.toppingID = t2.toppingId

where t1.toppingName = 'topping1'
and t2.toppingName = 'topping2'

Upvotes: 0

dnoeth
dnoeth

Reputation: 60462

To include the toppings you can do the COUNT-logic using Windowed Aggregate Functions:

SELECT p.pizzaID, p.pizzaName, dt.toppingName
FROM Pizza AS p 
JOIN
 (
   SELECT p.pizzaID, t.toppingName,
      COUNT(*) OVER (PARTITION BY p.pizzaID) AS cnt -- number of matching toppings
   FROM Pizza_Topping AS pt
   JOIN Topping AS t
     ON t.toppingID = pt.toppingID
   WHERE t.toppingName IN ('topping1', 'topping2')
 ) AS dt
  ON p.pizzaID = dt.pizzaID 
WHERE cnt = 2 -- number of searched toppings

Upvotes: 0

FutbolFan
FutbolFan

Reputation: 13713

I think this could be done more easily if you can get the count() in your subquery and then join back all the pizzaid's that have count greater than 1 something like this:

SELECT q1.pizzaID
    ,q1.PizzaName
    ,t.ToppingName
FROM (
    SELECT Pizza.pizzaID
        ,Pizza.pizzaName
        ,count(topping.ToppingName) total_count
    FROM Pizza
    INNER JOIN Pizza_Topping ON Pizza.pizzaID = Pizza_Topping.pizzaID
    INNER JOIN Topping ON Topping.toppingID = Pizza_Topping.toppingID
    GROUP BY Pizza.pizzaID
        ,Pizza.pizzaName
    HAVING count(topping.ToppingName) > 1
    ) q1
INNER JOIN Pizza_Topping pt ON q1.pizzaID = pt.pizzaID
INNER JOIN Topping t ON t.toppingID = pt.toppingID
WHERE t.toppingName in ('topping1', 'topping2')

Upvotes: 0

Java Main
Java Main

Reputation: 1581

Get your result without topping name . Wrap it in a view and do an inner join with Topping table .

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You are almost correct. You need to remove ToppingName from the GROUP BY:

GROUP BY Pizza.pizzaID, Pizza.pizzaName
HAVING COUNT(toppingName) >= 2

You also need to remove it from the SELECT if it is also there:

select p.pizzaID, p.pizzaName
from Pizza p join
     Pizza_Topping pt
     on p.pizzaID = pt.pizzaID join
     Topping t
     ont.toppingID = pt.toppingID
where t.toppingName in ('topping1', 'topping2')
group by p.pizzaID, p.pizzaName
having count(*) >= 2;

Also notice two things. Because you have a condition on toppings, the left join is unnecessary. You are only looking for matches, so inner join is appropriate. The use of table aliases makes the query easier to write and to read.

This assumes that pizzas cannot have duplicate toppings. If so, then change the last condition to:

having count(distinct t.toppingName) >= 2

Upvotes: 1

Related Questions