SQL Search based on multiple values without Intersect

Question

I am trying to search for items (members) that match 0 or more field values in a simple table. I have a solution using INTERSECT, but would like to know if there as a better/simpler solution.

Simplfied Scenario:

A user will select 0 to 3 fields as search criteria. For each chosen field, the user will select (combobox) a value. These field_ids and values will be passed to the database. Results matching the search will be returned to the user.

Example of results that I need to find:

1) I need to find all members that have a specific value for fld_id = 1, and another specific value for fld_id = 2 and another specific value for fld_id = 3 (i.e members that match all three selected id/value pairs).

2) I also need to find all member that have only a specific value for fld_id = 1

3) Etc...

The field/value pairs exist in the following table:

    TABLE [dbo].[fields](
      [fld_id] [int] NOT NULL,
      [member_id] [int] NOT NULL,
      [value] [varchar](2000) NOT NULL
    )

There will be, say, 100 different fields (fld_id) and 10 different members (member_id). **Not all members will have values for all fields.

I can get the results I want using a table variable (@tb_fields) and the INTERSECT clause:

Declare
    @member_id      INT,
    @company_id     INT,        
    -- fields
    @fld_id_1       int, 
    @fld_val_1      varchar(2000),
    @fld_id_2       int, 
    @fld_val_2      varchar(2000), 
    @fld_id_3       int, 
    @fld_val_3      varchar(2000)


SET @member_id      = 2307363
SET @company_id     = 101
--  fields
SET @fld_id_1       = 1
SET @fld_val_1      = 'value1'
SET @fld_id_2       = 2
SET @fld_val_2      = 'value2'
SET @fld_id_3       = 3
SET @fld_val_3      = 'value3' 

--Declare table vars
Declare @tb_company table ( company_id int PRIMARY KEY CLUSTERED )

Declare @tb_member table 
(
    id              [int] IDENTITY PRIMARY KEY CLUSTERED,  
    member_id       [int] Not NULL, 
    member_name     [varchar](100)
)

Declare tb_fields table(member_id int)

--populate table vars
Insert @tb_company Select @company_id

Insert into tb_fields (member_id)
    select distinct member_id 
        from 
            fields
        where 
            (fld_id = @fld_id_1 and value = @fld_val_1) 

    INTERSECT

    select distinct member_id 
        from 
            fields
        Where       
            (fld_id = @fld_id_2 and value = @fld_val_2)

    INTERSECT

    select distinct member_id 
        from 
            fields
        Where       
            (fld_id = @fld_id_3 and value = @fld_val_3)

--Output
SELECT
        distinct member_search.member_id, member_search.member_name     
    FROM 
        member_search inner join 
        @tb_company assn on member_search.company_id = assn.company_id left join 
        tb_fields flds on member_search.member_id = flds.member_id
    WHERE   
        flds.member_id is not null                      
    Order By
        member_search.member_name

However, I would like to know if there is a better way to do this.

Note: Dynamic sql queries are not an option.

Thanks you very much for any insight!

Answer 1

I decided to use The INTERSECT solution, even though the query plan indicated a bit of a performance hit.