SQL Server query to filter data by different values in the same column-CodePudding

I have following table:

Query 1 works fine and returns both records

select * 
from dbo.PersonalAttribute
where ProductId = '12345'

Query 2-3-4-5 works fine and filters on AttributeType and corresponding AttributeDescription.

select * 
from dbo.PersonalAttribute
where ProductId = '12345'
and (AttributeType = 'test1' and AttributeDescription = 0) -- will return one row


select * 
from dbo.PersonalAttribute
where ProductId = '12345'
and (AttributeType = 'test1' and AttributeDescription = 1) -- will return zero rows

select * 
from dbo.PersonalAttribute
where ProductId = '12345'
and (AttributeType = 'test2' and AttributeDescription = 1) -- will return one row

select * 
from dbo.PersonalAttribute
where ProductId = '12345'
and (AttributeType = 'test1' and AttributeDescription = 0) -- will return zero rows

Query 6 not working and returns 0 rows even all conditions are true:

select *
from dbo.PersonalAttribute
where ProductId = '12345'
and (AttributeType = 'test1' and AttributeDescription = 0) 
and (AttributeType = 'test2' and AttributeDescription = 1)

How can I rewrite Query 6 to have an ability to filter data by combination of vales in Attricute Type and Attribute Description columns

Thank you!

CodePudding user response：

The "and" means you are looking at the same row for two different things. You are allowed to pick up one fruit. You cannot pick up an apple and a pear. But you can pick up an apple or a pear. Change that "and" to "or" and wrap it in parenthesis:

select *
from dbo.PersonalAttribute
where ProductId = '12345'
and ((AttributeType = 'test1' and AttributeDescription = 0) 
 or (AttributeType = 'test2' and AttributeDescription = 1))

CodePudding user response：

One option is via a JOIN. You can create a Temp table or even a Table Variable to hold the desired combinations

select P.* 
 From  dbo.PersonalAttribute P
 Join ( values  ('test1',0)
               ,('test2',1)
      ) V(AttrT,AttrD)
  on  AttributeType        = AttrT
  and AttributeDescription = AttrD
  and  ProductId = '12345'