Ok, this is killing me. I feel like I'm missing something obvious; either a basic SQL concept that I know and that my brain is refusing to provide, or a basic SQL concept that I somehow never got.I need a query that will select all rows from table A which have rows in join table B that match (user-provided) table C. Here's DDL:create table #data (i int,name varchar(20))create table #attrs (i int,name varchar(20))create table #data_attrs (i_data int, i_attrs int)insert into #data (i,name) values (1,'data1')insert into #data (i,name) values (2,'data2')insert into #data (i,name) values (3,'data3')insert into #data (i,name) values (4,'data4')insert into #attrs (i,name) values (1,'attr1')insert into #attrs (i,name) values (2,'attr2')insert into #attrs (i,name) values (3,'attr3')insert into #attrs (i,name) values (4,'attr4')insert into #data_attrs (i_data,i_attrs) VALUES (1,1)insert into #data_attrs (i_data,i_attrs) VALUES (1,2)insert into #data_attrs (i_data,i_attrs) VALUES (2,1)insert into #data_attrs (i_data,i_attrs) VALUES (3,2)insert into #data_attrs (i_data,i_attrs) VALUES (3,4)insert into #data_attrs (i_data,i_attrs) VALUES (4,1)insert into #data_attrs (i_data,i_attrs) VALUES (4,2)insert into #data_attrs (i_data,i_attrs) VALUES (4,3)insert into #data_attrs (i_data,i_attrs) VALUES (4,4)
Right, so you've got a basic table of data, table of attributes, and a join table connecting the data to the attributes. Now I need to search, optionally for either an exact match or an "at least" match. The exact match will find only those data rows that have all of the attributes we're searching for, and only those attributes. The "at least" match will find all of the data rows that have all of the attributes that we're searching for, regardless of whether those rows also have other attributes. The search values are passed in to a SP via CSV and then parsed into a temp table. That part I've got. For the purposes of my question, you can consider the SP to have the following code instead of that parsing:declare @tSearch table (i_attrs int)insert into @tsearch (i_attrs) VALUES (1)insert into @tsearch (i_attrs) VALUES (2)/* Magic _exact_ code goes here and should returni name- ----1 data1 *//* Magic _at_least_ code goes here and should returni name- ----1 data14 data4 */
Needless to say, I'm trying to do this without cursors or dynamic SQL. The data table is medium sized; about 100,000 rows, while the attribute table is about 50 rows. It's conceivable that the query could be run for any number of attributes between 1 and 50. I'm pulling my hair out. What am I missing?!Thanks-b
Select arbitrary number of matching join rows?
Posted - 2004-04-13 : 01:56:21
