Using Where Clause With Two Type of 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.

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
)

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

Can we use multiple AND conditions for the same column in WHERE clause?

The condition StyleID = 15 AND StyleID = 24 can never return anything because it can never be true. So what you're getting is expected. If you want to get the customers that have both StyleID 15 and 24 (in different records), then you need to group the records by customer:

SELECT CONCAT(c.CustFirstName," ",c.CustLastName) AS CustomerName
FROM Musical_Preferences mp
JOIN Customers c ON c.CustomerID = mp.CustomerID
WHERE mp.StyleID IN(15, 24)
GROUP BY c.CustomerID, c.CustFirstName, c.CustLastName
HAVING COUNT(mp.CustomerID) = 2;

I added c.CustFirstName and c.CustLastName to the GROUP BY. Alternatively, you can only group by the ID and then use MIN() or MAX() on the first and last name. Both ways are more or less then same.

This query will only work if your data can never have a customer with the same StyleID more than once (example: customer 1001 has 3 records with StyleID 15, 24, and 24).

SQL where clause with two conditions on same column

The following query should work, and it's very intuitive in it's logic.

Query Explanation

Since a user could have two records in mail table, if he or she has received open as well as confirmation letters, so we look for only those users who have an open letter but no confirmation letter. We use NOT EXISTS to make sure that user has received no confirmation letter.

SELECT u.username, 
m.username,
m.subjectdescription
FROM users u
INNER JOIN mails m
ON m.username = u.username + '@hotmail.com'
WHERE m.subjectdescription LIKE '%open%'
AND NOT EXISTS (SELECT 1
FROM mails m1
WHERE m1.username = m.username
AND m1.subjectdescription LIKE '%confirmation%');

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


Related Topics



Leave a reply



Submit