No, I'm not talking about a basic "where" clause. I hope 
.I've got an app that needs to identify all users who match every row in a "request" table. I'm having no trouble matching users who meet one criteria, but I'm having great difficulty trying to only return those who match all criterian. A self-join on request table would make senhse, but there can be an arbitrary number of request rows, and I want to avoid dynamic sql if at all possible.Here's a test case and data:/* Table of user data */declare @cr table (u int,ica int,done tinyint,v tinyint)/* Requests */declare @req table (ica int,done tinyint,minv tinyint,maxv tinyint)insert into @cr (u,ica,done,v) select 1,3,0,1 union all select 1,5,1,3 union all select 2,5,1,3 union all select 2,140,0,3insert into @req (ica,done,minv,maxv) select 5,1,1,4 union all select 140,0,1,4
In this case, I want to return "2", since only u=2 matches both rows in the request table (ica is the same FK on both tables, done has to match exactly, and @cr.v has to be between @req.minv and @req.maxv).[code]select u from @cr cr join @req req on cr.ica=req.ica and cr.done=req.done and cr.v between req.minv and req.maxv[/quote]...is simple enough, but it returns both 1 and 2, because u=1 matches one row in req.Help!Thanks-b
Finding a row that matches all criteria?
Posted - 2006-01-05 : 20:07:55
