SELECTING with multiple WHERE conditions on same column
You can either use
GROUP BY and
HAVING COUNT(*) = _:
WHERE flag IN ('Volunteer', 'Uploaded', ...)
GROUP BY contact_id
HAVING COUNT(*) = 2 -- // must match number in the WHERE flag IN (...) list
contact_id, flag is unique).
Or use joins:
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
where PropertyID = 7
and RecordID in
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
Where Diagnosis_ID=299 or Diagnosis_ID=288
Group By Subject_ID
Can we use multiple AND conditions for the same column in WHERE clause?
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;
c.CustLastName to the
GROUP BY. Alternatively, you can only group by the ID and then use
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.
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.
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
CREATE TABLE #ProductMaster
, ProductName VARCHAR(50)
CREATE TABLE #ProductIngredients
VALUES(1 , 'floor'), (2 , 'salt'), (3 , 'sugar'), (4 , 'oil'), (5 , 'pepper');
VALUES(1 , 'chapati'), (2 , 'pizza bun'), (3 , 'chappati type 2'), (4 , 'pizza bun type 2');
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
FROM cte cte
WHERE EXISTS (SELECT * FROM cte t WHERE t.Ingredients = cte.Ingredients AND t.ProductId = 2);
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