Hi everybody.

I seen the different solution and have now try them in the real environment.

As I said in the beginning, My sql skills are not the best, but i know how to use the different tools, write simple sql code and ++

And I have deep respect for you guys who can find solution so fast.

My skills are more related towards Classic Asp, VB from many years ago.

I have try the solution from John Mitchell, ChrisM and ScottPletcher, and see big variation in speed.

I try them on a table with 32.000 recipe and linked to a allergen/recipe table with 323.000 records. This table is made from a stored procedure on the fly.

I have try different variation of recipe group and allergen, and find the following after several test.

The solution from John Mitchell takes an average of 1,06 sec to run on my chosen criteria.

The solution from ChrisM takes an average of 0,9 sec to run on my chosen criteria. Make use of Not Exists

The solution from Scott Pletcher takes an average of 0,12 sec to run on my chosen criteria. Make use of Group By and the case-then-else function.:-)

I have just tested the speed, nothing else.

I'm not familiar with SQL Query Profiler and have just check the speed of a query when it runs in the query windows in management Studio and check the elapsed time.

Each query I have run for 10 times, and use the exactly same data and criteria.

I also seen the advice from Kim Crosser, and some of them are implemented.

I got an allergentable with just 28 records, and use the Id of the allergen to relate to ingredient in the recipe.

I also think about writing the allergen into the recipe table, but i feel that this is a solution that is not the best. It will cause 28 new columns.

Next week I will implement this on my website, and then have some more speed test and get some input from my readers. A solution like this for people with allergy and diet problem, this is helpful. My biggest challenge is to update and maintain the data.

For me it is interesting to see how much the speed can be influence of using Not Exists, Group By, Indexes ++

And I think that this can bee pretty important for other one who build up query like this. When query should run on a website, speed is the most important factor

For the record:

My server is a Dell PowerEdge R420 with 64 GB RDIMM Ram and Intel Xeon E5-2440 2.40GHz. Windows 2012 Server with standard SQL 2012 server

MVH

Knut