Is there a SQL WHERE IN statement with AND logic?

Question

I am writing some code that deals with objects and tag clouds, and I am trying to come up with a generic SQL search solution.

I want to allow users to be able to construct searches in a UI of the tags associated with an object. For example, a user might search all the objects with a tag of 'ball', and 'red' to find all the red balls in the system.

Here is some sample code:

declare @Table table 
(
    [Name] varchar(50),
    [Prop] varchar(50)
)

insert @Table (name,prop) values ('thing 1','ball')
insert @Table (name,prop) values ('thing 1','red')
insert @Table (name,prop) values ('thing 1','foo') -- tag not part of search

insert @Table (name,prop) values ('thing 2','ball')
insert @Table (name,prop) values ('thing 2','green')

insert @Table (name,prop) values ('thing 3','square')
insert @Table (name,prop) values ('thing 3','red')

insert @Table (name,prop) values ('thing 4','square')
insert @Table (name,prop) values ('thing 4','blue')

select distinct name from @Table
where prop in
(
    'red','ball' -- returns thing 1, thing 2, thing 3
)

Now, an astute coder will quickly point out that this will produce an OR effect, grabbing objects tagged with 'Green' and 'Ball', which is not what we want.

Obviously I could just use WHERE with AND to hard code values. However, I was hoping to create a SP that passed in a table variable containing all the tags that the user wanted to AND together, and the IN syntax would let me just select from that table parameter.

So, my question is this: Is there any SQL syntax or statement that can AND an arbitrary number of values? I don't know of any, and I couldn't find anything about that doing a quick search. Alternately, is there a way this could be restructured so that I can get and ANDed result with an arbitrary number of variables?

I could solve this with a messy code generating dynamic SQL script, but I need performance. This isn't really an acceptable solution.

I have tried to play with De Morgan's laws to see if I could flip the problem, but that fails because of how the SQL logic is executed.

select distinct name from @Table
where name not in
(
    select name from @Table
    where prop not in
    (
     'red','ball'
    )
)

This fails because it looks for the ANDed tags, but omits any results that don't exactly match the tags. (Thing 1 has a tangental property of 'foo' that is preventing it from being included. Essentially it is looking for objects that match 'red' and 'ball', but have no other properties, which doesn't work)

So, any suggestions? This is a tricky one...

Amy B, fastest gun in the west! That question in turn references https://stackoverflow.com/questions/163887/sql-query-simulating-an-and-over-several-rows-instead-of-sub-querying#163907 which is also a good answer to the question. Thanks! — Roger Hill, Feb 04 '18 at 00:00

score 2 · Accepted Answer · answered Feb 04 '18 at 00:00

2

You do this with group by and having. For your case`:

select name
from @Table
where prop in ('red', 'ball')
group by name
having count(distinct prop) = 2;

answered Feb 04 '18 at 00:00

Gordon Linoff

1,242,037
58
646
786

Is there a SQL WHERE IN statement with AND logic?

1 Answers1