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)
Comments
Post a Comment