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
Jsonb Array Contains Like or and and Operators
Sql Select Radius Search Based on Latitude Longitude
Oracle SQL Get the First and Last Records from an Ordered Dataset
Select and Count in One Query from Two Tables With MySQL
Mysql Split Comma Separated String into Temp Table
Sql Select Count for Multiple Columns in a Single Query
Mysql: Error Code: 1054. Unknown Column in 'Where Clause'
How to Find Multiple Occurrence of Particular String and Fetch Value in SQL Server
How to Get the Month and Day With Leading 0'S in SQL (E.G. 9 => 09)
Sql Query to Show Missing Records from Another Table
Postgresql Query to Return Results as a Comma Separated List
A SQL Query to Get All the Records Where 5 Columns Are Same But Only One Column Is Different
How to Retrieve Records for Last 30 Minutes in Ms SQL
How to Parser a SQL Query to Pull Out the Column Names and Table Names
Custom Aggregate Function (Concat) in SQL Server
How to Remove Line Feed Characters When Selecting Data from SQL Server