2

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.

1

2 Answers 2

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

1
  • thank you so much, that's what I was looking for. But a half-*ssed answer was the best I could come up with on my own, and I lack the vocabulary to properly describe the problem.
    – asasaki
    May 11, 2023 at 16:02
1

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) 
1
  • Nore, after 24hrs you can tick it to indicate it is the answer. May 10, 2023 at 23:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.