I'm starting to pull my hair out (not much left, actually).
I have a simple table construct with two tables plus an NM-table in between. To illustrate this, I've tried to abstract this down to something (hopefully) easy to understand.
Let's assume I have a table of products (#Products in the SQL below). I also have a table (#Combos in the SQL below) that merges those products into a list of valid combinations (call these "product packages", if you like). Combinations
may thus contain 1..n products each. As a result, I have a NM-table that connects #Products and
#Combos (#CombosProducts in the SQL below).
For instance, I could have one combo that consists of product 1 only, then one that consists of product 1 and product 2, then one with product 1 and three and so on.
At runtime of an application, I now get a list of products (think of this as a view) and now need to find the record in
#Combos that references the same combination of products.
Here's a script that I hope illustrates the problem:
-- Create some (temporary) tables
CREATE TABLE #Combos ( ComboID varchar(10) NOT NULL );
CREATE TABLE #Products ( ProductID varchar(10) NOT NULL );
CREATE TABLE #CombosProducts ( ComboID varchar(10), ProductID varchar(10) );
View Complete Post