March 13, 2013 at 7:42 am
Hi,
I have the following situation. I have a list of items that I am excluding from a query. So far I have been using a WHERE statement and NOT LIKE and NOT LIKE. The list keeps growing, so I thought why not just create a temporary table based on a text file that contains the list of items I want to exclude. This was easy.
The problem is that this temporary table contains just one column and I have nothing to join it to.
So I am hoping someone has some advice on how to best handle a list of items you want to exclude, other than having numerous not like statements.
March 13, 2013 at 7:47 am
It would be easier for us to help you if you provide some DDL along with sample data and the expected results
This would also help us to give you a tested solution back
Please read the link in my signature if you don't know how to do this..
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 13, 2013 at 8:05 am
I hope this helps. The list to exclude is much longer than this.
SELECT MODEL
FROM CARS
WHERE MODEL NOT LIKE '%S2%', MODEL NOT LIKE '%R5%', MODEL NOT LIKE '%G8%',
MODEL NOT LIKE '%Z20%', MODEL NOT LIKE '%TX%', MODEL NOT LIKE '%E45%', MODEL NOT LIKE '%FG3%'
You could create a temp table with the list of models you want to exclude
declare @MyFile syshane;
CREATE TABLE #tempfile (ModelList VARCHAR (300))
EXEC ('BULK INSERT #tempfile FROM "' + @MyFile + '"')
SELECT * FROM #tempfile
DROP TABLE #tempfile
March 13, 2013 at 8:08 am
Sounds, off the top of my head as if you need to normalize your table more. Create a table to hold the car models with key that you can reference in your table cars. Then you can create your exclusion table with the same referencing key for exclusion purposes.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply