sql - Selecting from Table A where it joins to all data in Table B -

i have table of documents, , table of tags. documents tagged various values.

i attempting create search of these tags, , part working. however, getting results returned when matches tag. want results matches tags.

i have created illustrate problem http://sqlfiddle.com/#!3/8b98e/11

tables , data:

create table documents (  docid int,  doctext varchar(500) );  create table tags (   tagid int,   tagname varchar(50) );  create table documenttags (   doctagid int,   docid int,   tagid int,   value varchar(50) );  insert documents values (1, 'document 1 text'); insert documents values (2, 'document 2 text');  insert tags values (1, 'tag name 1'); insert tags values (2, 'tag name 2');  insert documenttags values (1, 1, 1, 'value 1'); insert documenttags values (1, 1, 2, 'value 2'); insert documenttags values (1, 2, 1, 'value 1'); 

code:

-- set parameters declare @tagxml varchar(max) set @tagxml = '<tags>                   <tag>                     <description>tag name 1</description>                     <value>value 1</value>                   </tag>                   <tag>                     <description>tag name 2</description>                     <value>value 2</value>                   </tag>                 </tags>'  -- create table store parsed xml in declare @xmltagdata table  (     id varchar(20)     ,[description] varchar(100)     ,value varchar(250) )  -- populate our xml table declare @itag int exec sp_xml_preparedocument @itag output, @tagxml -- execute select statement uses openxml rowset provider -- produce table our xml structure , insert our temp table insert @xmltagdata (id, [description], value) select  id, [description], value    openxml (@itag, '/tags/tag',1)         (id varchar(20),                 [description] varchar(100) 'description',                 value varchar(250) 'value')  execute sp_xml_removedocument @itag  -- update xml table id's match existsing tag id's update      @xmltagdata set         x.id = t.tagid        @xmltagdata x inner join  tags t on x.[description] = t.tagname  -- check looks right --select *  --from @xmltagdata  -- things not quite work. both doc 1 & 2 back,  -- want document 1. -- i.e. documents have both tags matching values select distinct d.* documents d inner join documenttags t on t.docid = d.docid inner join @xmltagdata x on x.id = t.tagid , x.value = t.value 

(note not dba, there may better ways of doing things. on right track, open other suggestions if implementation can improved.)

can offer suggestions on how results have tags?

many thanks.

use option [not] exists , except operators in last query

select * documents d not exists (                   select x.id , x.value                   @xmltagdata x                    except                   select t.tagid, t.value                   documenttags t                   t.docid = d.docid                   ) 

demo on sqlfiddle

or

select * documents d exists (               select x.id , x.value               @xmltagdata x                except               select t.tagid, t.value               documenttags t               t.docid != d.docid               )    

demo on sqlfiddle

or

also can use simple solution xquery methods: nodes(), value()) , cte/subquery.

-- set parameters declare @tagxml xml set @tagxml = '<tags>                   <tag>                     <description>tag name 1</description>                     <value>value 1</value>                   </tag>                   <tag>                     <description>tag name 2</description>                     <value>value 2</value>                   </tag>                               </tags>'                  ;with cte  (   select tagvalue.value('(./value)[1]', 'nvarchar(100)') value,          tagvalue.value('(./description)[1]', 'nvarchar(100)') [description]          @tagxml.nodes('/tags/tag') t(tagvalue)   )   select *   documents d   not exists (                     select t.tagid, c.value                     cte c join tags t with(forceseek)                        on c.[description] = t.tagname                     except                     select t.tagid, t.value                     documenttags t with(forceseek)                     t.docid = d.docid                                               ) 

demo on sqlfiddle

or

-- set parameters declare @tagxml xml set @tagxml = '<tags>                   <tag>                     <description>tag name 1</description>                     <value>value 1</value>                   </tag>                   <tag>                     <description>tag name 2</description>                     <value>value 2</value>                   </tag>                               </tags>'          select *   documents d   not exists (                     select t2.tagid,                            tagvalue.value('(./value)[1]', 'nvarchar(100)') value                                                @tagxml.nodes('/tags/tag') t(tagvalue)                       join tags t2 with(forceseek)                         on tagvalue.value('(./description)[1]', 'nvarchar(100)') = t2.tagname                                                             except                     select t.tagid, t.value                     documenttags t with(forceseek)                     t.docid = d.docid                                            ) 

demo on sqlfiddle

in order improving performance(forced operation of index seek on tags , documenttags tables), use indexes , table hints(forceseek hint added query above):

create index x on documents(docid) include(doctext) create index x on tags(tagname) include(tagid) create index x on documenttags(docid) include(tagid, value)