CODEWITHSUNDEEP
sqlsql-server
Dan Cundy
Dan Cundy

Reputation: 2849

Add more than one filter to a query using same column

Background

I am trying to create a filter that searches database and displays results based on certain ID's

CODE

WHERE DATEDIFF(YEAR, A.AcquiredDate, GetDate())  >=5  AND A.AssetTypeID = 7 AND A.CategoryID = 5

What i would like it to say:

WHERE DATEDIFF(YEAR, A.AcquiredDate, GetDate())  >=5  AND A.AssetTypeID = 7 AND A.CategoryID = 5 AND A.CategoryID = 4

Problem

After adding second part of code AND A.CategoryID = 4 the query spits out 0 records.

Question

How do I filter both A.CategoryID = 5 AND A.CategoryID = 4?

Upvotes: 0

Views: 74

Answers (2)

Robert Bain
Robert Bain

Reputation: 9576

You can use an OR or an IN. So:

WHERE DATEDIFF(YEAR, A.AcquiredDate, GetDate())  >=5  AND A.AssetTypeID = 7 AND (A.CategoryID = 4 OR A.CategoryID = 5)

Or:

WHERE DATEDIFF(YEAR, A.AcquiredDate, GetDate())  >=5  AND A.AssetTypeID = 7 AND A.CategoryID IN (4, 5)

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93704

Change the Where condition like this. Use OR operator or IN Clause instead of AND

The AND operator displays a record if both the first condition AND the second condition are true.

The OR operator displays a record if either the first condition OR the second condition is true.

WHERE DATEDIFF(YEAR, A.AcquiredDate, GetDate())  >=5  
AND   A.AssetTypeID = 7 
AND   (A.CategoryID = 5 or A.CategoryID = 4)

OR

WHERE DATEDIFF(YEAR, A.AcquiredDate, GetDate())  >=5  
AND   A.AssetTypeID = 7 
AND   A.CategoryID IN (5,4)

Upvotes: 3

Related Questions