Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Transact-SQL (2005)  selecting data based on associated tags

Author  Topic  rdaunce Starting Member 7 Posts Posted - 2009-10-14 : 21:02:11 I have an issue that I have been trying to solve and I haven't been able to find much info on different approaches to take. Basically, I have 4 tables - statements, statement_tag, tag, and tagtype. There are a number of statements and then a number of tags with different types. Tags are applied to statements using the statement_tag table. Here is example data I am working with: declare @statement table ( statementid int, name varchar(255) ) insert into @statement (statementid, name) values (1, 'statement 1') insert into @statement (statementid, name) values (2, 'statement 2') insert into @statement (statementid, name) values (3, 'statement 3') insert into @statement (statementid, name) values (4, 'statement 4') insert into @statement (statementid, name) values (5, 'statement 5') insert into @statement (statementid, name) values (6, 'statement 6') insert into @statement (statementid, name) values (7, 'statement 7') insert into @statement (statementid, name) values (8, 'statement 8') insert into @statement (statementid, name) values (9, 'statement 9') declare @tagtype table ( tagtypeid int, tagtype varchar(50) ) insert into @tagtype (tagtypeid, tagtype) values (1, 'type 1') insert into @tagtype (tagtypeid, tagtype) values (2, 'type 2') insert into @tagtype (tagtypeid, tagtype) values (3, 'type 3') declare @tag table ( tagid int, tagtypeid int, tag varchar(50) ) insert into @tag (tagid, tagtypeid, tag) values (1, 1, 'tag 1') insert into @tag (tagid, tagtypeid, tag) values (2, 1, 'tag 2') insert into @tag (tagid, tagtypeid, tag) values (3, 1, 'tag 3') insert into @tag (tagid, tagtypeid, tag) values (4, 2, 'tag 4') insert into @tag (tagid, tagtypeid, tag) values (5, 2, 'tag 5') insert into @tag (tagid, tagtypeid, tag) values (6, 2, 'tag 6') insert into @tag (tagid, tagtypeid, tag) values (7, 3, 'tag 7') insert into @tag (tagid, tagtypeid, tag) values (8, 3, 'tag 8') declare @statement_tag table ( statementid int, tagid int ) insert into @statement_tag (statementid, tagid) values (1, 1) insert into @statement_tag (statementid, tagid) values (2, 1) insert into @statement_tag (statementid, tagid) values (3, 1) insert into @statement_tag (statementid, tagid) values (4, 2) insert into @statement_tag (statementid, tagid) values (5, 2) insert into @statement_tag (statementid, tagid) values (6, 2) insert into @statement_tag (statementid, tagid) values (7, 3) insert into @statement_tag (statementid, tagid) values (8, 3) insert into @statement_tag (statementid, tagid) values (9, 3) insert into @statement_tag (statementid, tagid) values (1, 4) insert into @statement_tag (statementid, tagid) values (2, 5) insert into @statement_tag (statementid, tagid) values (3, 6) insert into @statement_tag (statementid, tagid) values (4, 4) insert into @statement_tag (statementid, tagid) values (5, 5) insert into @statement_tag (statementid, tagid) values (6, 6) insert into @statement_tag (statementid, tagid) values (7, 4) insert into @statement_tag (statementid, tagid) values (8, 5) insert into @statement_tag (statementid, tagid) values (9, 6) insert into @statement_tag (statementid, tagid) values (1, 7) insert into @statement_tag (statementid, tagid) values (2, 7) insert into @statement_tag (statementid, tagid) values (3, 7) insert into @statement_tag (statementid, tagid) values (4, 7) insert into @statement_tag (statementid, tagid) values (5, 7) insert into @statement_tag (statementid, tagid) values (6, 8) insert into @statement_tag (statementid, tagid) values (7, 8) insert into @statement_tag (statementid, tagid) values (8, 8) insert into @statement_tag (statementid, tagid) values (9, 8) The user will be supplying a list of tags that will be stored in a table. This list will change based on user actions, so the solution needs to be adaptable to work with any table of tagids. declare @tags table ( tagid int ) insert into @tags (tagid) values (1) --tagtype 1 insert into @tags (tagid) values (2) --tagtype 1 insert into @tags (tagid) values (4) --tagtype 2 What I need to do is to select all statements that match at least one of the provided tags for each of the tag types that are relevent. In the above example, only two of the tagtypes are represented, however, this will not always be the case. In some instances only one tagtype or all tagtypes are used. I need to ignore tagtypes that are not used. For the above example, I need to return the following all statements that match types ( ( 1 or 2 ) and 4 ): Statement 1 (matches tagid 1, 4) Statement 4 (matches tagid 2, 4) The best I have been able to come up with is the following: declare @vartags table ( tagtypeid int, tagid int ) declare @varstatement table ( statementid bigint ) declare @vartagtype table ( tagtypeid int ) insert into @vartags select t.tagtypeid, t.tagid from @tags ts inner join @tag t on ts.tagid = t.tagid insert into @vartagtype select distinct tagtypeid from @vartags declare @tagtypeid int select top 1 @tagtypeid=tagtypeid from @vartagtype if @tagtypeid is not null begin insert into @varstatement select s_t.statementid from @statement_tag s_t where s_t.tagid in (select tagid from @vartags where tagtypeid = @tagtypeid) delete from @vartagtype where tagtypeid = @tagtypeid select top 1 @tagtypeid=tagtypeid from @vartagtype while @@rowcount > 0 begin delete from @statement where statementid not in ( select s.statementid from @statement_tag s_t join @statement s on s.statementid = s_t.statementid where s_t.tagid in (select tagid from @vartags where tagtypeid = @tagtypeid) ) end end select * from @varstatement vs join @statement s on s.statementid = vs.statementid While this works, I don't think that it is the most efficient way to go about this. I'm really struggling to come up with an alternative way to handle the list of tags and the fact that the number of tags provided as well as the number of corresponding tagtypes will vary. Can anyone suggest a better solution? --Bob