Complicated relationship Q

Question

I'm trying to do a query to find products that aren't on the customer's authorized products list, and it's breaking my brain.

Each customer has an authorized product group list, and within a product group each entry might consist of a Product ID, a Product Category ID, Vendor Number, Price Schedule ID, or Brand ID (all different columns on the Product record).

Many authorized product group lists have a mix of 2 or more kinds of entries, and often there's some overlap between the different columns used as selection criteria. Of course, there's a code on the product group list item that indicates which column the entry is based on, so it's easy enough to parse.

On the Product side, I'd be looking for products without an "Inactive" flag that aren't on the authorized list for each customer.

The tables look roughly like this:

Customer

CustomerID	AuthorizedListID
1	345
2	345
3	117

Authorized List

ListID	ListName
117	Frozen goods
345	Pizza

Authorized List Items

ListKey	ItemRefNo	Type
117	68	BRND
117	202	ITEM
117	1415	ITEM
117	3012	ITEM
117	61152	CATG
117	31667	PRIC
345	9615	ITEM
345	2100	ITEM
345	13229	VNDR
345	87211	CATG

Products

ProductID	CategoryKey	PriceKey	VendorKey	BrandKey	Inactive
202	61152	113	45529	16	0
1415	4990	5601	882	41009	0
2100	61152	37772	9101	20900	0
3557	87211	4101	17743	23777	0
4027	5193	644	882	68	0
4028	5193	580	882	68	0
9615	87211	1512	17743	8738	0
13017	87211	4040	13229	23795	0
42500	87211	20155	17743	4355	0
53529	1011	31667	13229	602	1

While I have a fair bit of SQL experience, this has a level of complexity that's beyond me at this point...so part of what's messing me up is that I don't even know how to succinctly and accurately describe what I'm trying to do so I can do a search for it. I don't even know the appropriate tags for this question. Also, I forgot to mention earlier: T-SQL for SQL Server 2016R2.

Expected results would be a set of records with CustomerID and ProductID columns where the product wasn't in the authorized list for the customer and Inactive = 0. Ideally, this would be a view.

I've gotten as far as this query:

SELECT C.*, 
A.ItemRefNo,
A.Type,
CASE 
    WHEN CA.ProductId IS NOT NULL THEN CA.ProductId 
    WHEN B.ProductId IS NOT NULL THEN B.ProductId
    WHEN V.ProductId IS NOT NULL THEN V.ProductId 
    WHEN P.ProductId IS NOT NULL THEN P.ProductId 
    WHEN A.Type = 'ITEM' THEN A.ItemRefNo 
    ELSE NULL 
    END
AS ProductId
FROM Customer C
INNER JOIN authorizedListItems A
ON C.AuthorizedListId = A.ListKey 
LEFT JOIN products CA
    ON A.ItemRefNo = CA.CategoryKey 
    AND A.Type = 'CATG'
LEFT JOIN products B
    ON A.ItemRefNo = B.BrandKey 
    AND A.Type = 'BRND' 
LEFT JOIN products V
    ON A.ItemRefNo = V.VendorKey 
    AND A.Type = 'VNDR' 
LEFT JOIN products P
    ON A.ItemRefNo = P.PriceKey 
    AND A.Type = 'PRIC' 

Query results:

CustomerId	AuthorizedListId	ItemRefNo	Type	ProductId
3	117	68	BRND	4027
3	117	68	BRND	4028
3	117	202	ITEM	202
3	117	1415	ITEM	1415
3	117	3012	ITEM	3012
3	117	61152	CATG	202
3	117	61152	CATG	2100
1	345	9615	ITEM	9615
2	345	9615	ITEM	9615
1	345	2100	ITEM	2100
2	345	2100	ITEM	2100
1	345	13229	VNDR	13017
1	345	13229	VNDR	53529

The result I'd like to see is:

CustomerId	ProductId
1	202
1	1415
1	4027
1	4028
2	202
2	1415
2	4027
2	4028
3	3557
3	9615
3	13017
3	42500

Let me say in advance, I know I wasn't filtering inactive items in the query and the results include superfluous columns.

Answer 1

You need to cross-join Product and Customer, then anti-join AuthorizedListItem which you can do using a NOT EXISTS. Do not use NOT IN as it doesn't deal with nulls properly.

While in theory you could use a bunch of AND/OR conditions to make the join, it's probably better to do four separate subqueries.

CREATE OR ALTER VIEW UnauthorizedProductsPerCustomer
AS
SELECT
  c.CustomerID,
  p.ProductID
FROM dbo.Customer c
CROSS JOIN dbo.Product p
WHERE NOT EXISTS (SELECT 1
    FROM AuthorizedListItem ali
    WHERE ali.ListKey = c.AuthorizedListID
      AND ali.ItemRefNo = p.CategoryKey
      AND ali.Type = 'CATG')
  AND NOT EXISTS (SELECT 1
    FROM AuthorizedListItem ali
    WHERE ali.ListKey = c.AuthorizedListID
      AND ali.ItemRefNo = p.PriceKey
      AND ali.Type = 'PRIC')
  AND NOT EXISTS (SELECT 1
    FROM AuthorizedListItem ali
    WHERE ali.ListKey = c.AuthorizedListID
      AND ali.ItemRefNo = p.VendorKey
      AND ali.Type = 'VNDR')
  AND NOT EXISTS (SELECT 1
    FROM AuthorizedListItem ali
    WHERE ali.ListKey = c.AuthorizedListID
      AND ali.ItemRefNo = p.BrandKey
      AND ali.Type = 'BRND')
  AND p.Inactive = 0;

SQL Fiddle

Answer 2

And the answer is:

If I create a view "AuthorizedProductsPerCustomer" using the query above, this query gives the result I was looking for:

SELECT C.CustomerId,
  P.ProductId 
FROM customer C 
CROSS JOIN products P 
WHERE P.ProductId NOT IN 
  (SELECT ProductId 
    FROM AuthorizedProductsPerCustomer A
    WHERE A.CustomerId = C.CustomerId)