Selecting With Multiple Where Conditions on Same Column

SELECTING with multiple WHERE conditions on same column

You can either use GROUP BY and HAVING COUNT(*) = _:

SELECT contact_id
FROM your_table
WHERE flag IN ('Volunteer', 'Uploaded', ...)
GROUP BY contact_id
HAVING COUNT(*) = 2 -- // must match number in the WHERE flag IN (...) list

(assuming contact_id, flag is unique).

Or use joins:

SELECT T1.contact_id
FROM your_table T1
JOIN your_table T2 ON T1.contact_id = T2.contact_id AND T2.flag = 'Uploaded'
-- // more joins if necessary
WHERE T1.flag = 'Volunteer'

If the list of flags is very long and there are lots of matches the first is probably faster. If the list of flags is short and there are few matches, you will probably find that the second is faster. If performance is a concern try testing both on your data to see which works best.

SQL - Multiple conditions where clause the same column

select  Subject_ID from (
     Select Distinct Subject_ID, Diagnosis_ID
       From
       Table_1
       Where Diagnosis_ID=299 or Diagnosis_ID=288
)
Group By Subject_ID
Having count(Subject_ID)>=2

select query with multiple conditions on same column

When 2 products have same ingredients, it is considered as alternative products. Each product has multiple ingredients - multiple rows. It is difficult compare values crossing rows. If each product has only 1 ingredient - combine multiple rows to 1 value, it is a lot easier to identify what products have same ingredients.

CREATE TABLE #IngredientsMaster
(IngredientId INT
,IngredientName VARCHAR(50)
);
CREATE TABLE #ProductMaster
(ProductId INT
, ProductName VARCHAR(50)
);
CREATE TABLE #ProductIngredients
(IngredientId INT 
,ProductId INT
);
INSERT #IngredientsMaster
VALUES(1 , 'floor'), (2 , 'salt'), (3 , 'sugar'), (4 , 'oil'), (5 , 'pepper');

INSERT #ProductMaster
VALUES(1 , 'chapati'), (2 , 'pizza bun'), (3 , 'chappati type 2'), (4 , 'pizza bun type 2');

INSERT #ProductIngredients  
VALUES (1, 1), (2, 1), (3, 1), (1, 2), (2, 2), (3, 2), (4, 2), (1, 3), (2, 3), (3, 3), (1, 4), (2, 4), (3, 4), (4, 4)

;WITH cte AS (SELECT pg.ProductId, Productname
                , STUFF((SELECT '-' + cast(t.IngredientId AS VARCHAR(3)) 
                FROM #ProductIngredients t
                WHERE t.productId = pg.productId
                ORDER BY t.ingredientId
                FOR XML PATH('')
                ), 1, 1, '') AS Ingredients
FROM #ProductIngredients pg
INNER JOIN #IngredientsMaster i
ON i.IngredientId = pg.IngredientId
INNER JOIN #ProductMaster p
ON p.ProductId = pg.ProductId
GROUP BY pg.ProductId, Productname
)
SELECT *
FROM cte cte
WHERE EXISTS (SELECT * FROM cte t WHERE t.Ingredients = cte.Ingredients AND t.ProductId = 2); 

Search for multiple conditions on same column?

First select all users having the wanted languages, count the languages and then restrict the users to have the language count equal to the count of the wanted languages.

SELECT user_id
  FROM (SELECT used_id,COUNT(*)
          FROM [table_name]
         WHERE language_id IN (1,2,3)
         GROUP BY user_id
        HAVING COUNT(*)=3
       ) i;

SQL Server query for multiple conditions on the same column

Try this:

DECLARE @tbl table ( [name] varchar(20) not null, groups int NOT NULL );

INSERT INTO @tbl VALUES
    ('a', 35), ('a', 36), ('b', 35), ('c', 36), ('d', 37);

DECLARE @group int = 35;

; WITH cte AS (
    SELECT
        [name]
        , COUNT ( DISTINCT groups ) AS distinct_group_count
    FROM @tbl
    WHERE
        groups >= @group
    GROUP BY
        [name]
)
SELECT t.* FROM @tbl AS t
INNER JOIN cte
    ON t.[name] = cte.[name]
WHERE
    cte.distinct_group_count > 1
    OR t.groups > @group;

RETURNS

+------+--------+
| name | groups |
+------+--------+
| a    |     35 |
| a    |     36 |
| c    |     36 |
| d    |     37 |
+------+--------+

Basically, this restricts the name results to groups with a value >= 35 with more than one distinct group associated, or any name with a group value greater than 35. Several assumptions were made in regard to your data, but I believe the logic still applies.

MYSQL SELECT with INNER JOIN with multiple WHERE/CONDITIONS from the same column

Try,

SELECT p.* 
FROM products p
INNER JOIN product_details pd ON p.id = pd.id_product 
                              AND pd.details IN ('quadra mar', 'prédio novo');

Take a look in your WHERE clause, I think an OR condition will fix your issue too.

... WHERE pd.details = 'quadra mar' OR pd.details = 'predio novo'

And if you want only products that contains all the conditions in where clause, you can try:

SELECT pd.id_product, pd.details 
FROM products p
INNER JOIN product_details pd ON p.id = pd.id_product 
                              AND pd.details IN ('quadra mar', 'prédio novo') 
GROUP BY pd.id_product, pd.details 
HAVING COUNT(*) > 1;

Multiple conditions on the same column in the WHERE clause

select PropertyVal
from your_table
where PropertyID = 7
and RecordID in 
(
  select RecordID   
  from your_table
  where (PropertyID = 13 AND PropertyVal='Business Development Analyst')
     or (PropertyID = 11 AND PropertyVal = 'Chicago')
  group by RecordID   
  having count(distinct PropertyID) = 2
)

---

Related Topics