SQL Statement Using Where Clause with Multiple Values

SQL Statement using Where clause with multiple values

Try this:

select songName from t
where personName in ('Ryan', 'Holly')
group by songName
having count(distinct personName) = 2

The number in the having should match the amount of people. If you also need the Status to be Complete use this where clause instead of the previous one:

where personName in ('Ryan', 'Holly') and status = 'Complete'

SQL Where Clause with multiple values

just use an IN clause

where number in (123, 127, 130)

you could also use OR (just for info, I wouldn't use this in that case)

where number = 123 or
      number = 127 or
      number = 130

Don't forget round braces around the "ors" if you have other conditions in your where clause.

SQL statement WHERE clause condition with multiple values

Use IN clause

SELECT count(*) AS totalRaces, a.races FROM bookedevent be INNER JOIN 
account a 
ON be.bookedEventBY = a.accountName 
WHERE be.eventID in(70,69,55)
GROUP BY a.races

Where clause with multiple values

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')

SQL search query with multiple values using in statement

You may order using a CASE expression:

SELECT *
FROM Component
WHERE ID IN (1,8,3)
ORDER BY
    CASE WHEN ID = 1 THEN 1
         WHEN ID = 8 THEN 2
         ELSE 3 END;

But a much better long term solution might be to maintain a separate table of target ID values and their ordering:

WITH cte AS (
    SELECT 1 AS ID, 1 AS position UNION ALL
    SELECT 8, 2 UNION ALL
    SELECT 3, 3
)

SELECT c.*
FROM Component c
INNER JOIN cte t
    ON c.ID = t.ID
WHERE c.ID IN (1,8,3)
ORDER BY t.position;

Query with where clause checking a variable with multiple values

It is not possible to define a list as a variable. You would have to declare your variable as a temporary table.

In your use case, you seem to be looking for dynamic sql.

declare @myList varchar(100)
set @myList = '( "Owner", "Admin", "Rule" )'
exec('SELECT * FROM product.text_data WHERE product.text_data.field_ref IN' + @myList)

Left join with multiple values in where clause

Next time, please, provide the data in text format, or event better in a SQL Fiddle like this: https://www.db-fiddle.com/f/aYULH8tP5yVB18ffkJNvFe/0

Try to organize your queries so that they can be more readable.

The problem was that you added the Contact conditions in the WHERE, by doing so, Broadcast will also be filtered, you must add all the Contact conditions in the LEFT JOIN.

To get the desired output you showed in the image (listing all broadcasts), you can't filter the broadcast:

SELECT b.id, 
       b.name, 
       COUNT(c.broadcast_Id) AS Recepients 
FROM Broadcast b 
LEFT JOIN Contact c ON b.id = c.broadcast_Id 
                   AND c.Created_by = 1 
                   AND c.isdeleted = 0
GROUP BY b.id,
         b.name

But to get the output you described (showing only what is not deleted and filtered by a specific user) is this:

SELECT b.id, 
       b.name, 
       COUNT(c.broadcast_Id) AS Recepients 
FROM Broadcast b 
LEFT JOIN Contact c ON b.id = c.broadcast_Id 
                   AND c.Created_by = 1 
                   AND c.isdeleted = 0
WHERE b.IsDeleted = 0 
  AND b.Created_by = 1
GROUP BY b.id,
         b.name

---

Related Topics