Re: finding rows that contain a set but not anything else....

David, You can turn this around and say: "tell me the numbers which don't have numbers in names other than what I have". If you can successfully put an index on (number, name) you can use that better than the table scan of your NOT IN example (index scans will read far less data). It only has to index-scan the matching numbers. You're looking to see if the same number exists in other names, so this query would work. SELECT a.number FROM table a WHERE (a.name=X OR a.name=Y) /* Qualify the possible hits here */ AND NOT EXISTS /* Disqualify the hits if also elsewhere */ (SELECT 1 /* This is called a correlated subquery */ FROM table b WHERE a.number = b.number AND b.name <> X AND b.name <> Y) Is this always going to take in two names? If so, you can use the structure above. If it is list-based, such as when you would populate a temp table, it would be something like: CREATE TABLE #tmp (name char(...)) INSERT into #tmp values ("X") INSERT into #tmp values ("Y") ... whatever quantity you need SELECT a.number FROM table a, #tmp t WHERE t.name = a.name /* Qualify the possible hits here */ AND NOT EXISTS /* Disqualify the hits if also elsewhere */ (SELECT 1 FROM table b WHERE a.number = b.number AND b.name <> a.name) t -> a -> b Not exists typically faster than NOT IN type subqueries. David M Rosner wrote: > > I have a table with 2 columns: name , number. I want to get all numbers > where name equals X or Y but not anything else. If i do a simple "where > name=X or name=Y" it will bring back numbers that could also have another > name. Here is sample data to illustrate this: > > name number > X 1 > X 2 > Y 2 > Y 1 > Z 1 > > For the data above i just want to return "2". I don't want 1 because there > Z has it as a number. Here is the query i came up with. > > SELECT number FROM table > WHERE name = X or name=Y AND> number NOT IN > ( SELECT number FROM table > WHERE name <> X AND name <> Y) > > This works, but this table is going to get to be as large as 50k rows and > this would really tax the server. In addition the query is going to be much > more complex. Can't i create a truth table or something and write functions > to insert/update/delete the table? Is this standard practice, or is there a > better way to do this? -- John McVicker Principal Consultant, District Lead Architect Sybase Professional Services Pennsylvania/New Jersey District 301-896-1765 mcvicker@sybase.com, mcvicker@bellatlantic.net, JohnMcVicker@compuserve.com http://www.sybase.com http://www.powersoft.com http://www.isug.com