How to select values from two tables that are not contained in the map table?

Question

Lets say I have the following tables:

• Customers
• Products
• CustomerProducts

Is there a way I can do a select from the Customers and Products tables, where the values are NOT in the map table? Basically I need a matched list of Customers and Products they do NOT own.

Another twist: I need to pair one customer per product. So If 5 customers do not have Product A, only the first customer in the query should have Product A. So the results would look something like this:

(Assume that all customers own product B, And more than one customer owns products A, C, and D)

1. Customer 1, Product A
2. Customer 2, Product C
3. Customer 3, Product D

Final twist: I need to run this query as part of an UPDATE statement in SQL Sever. So I need to take the value from the first row:

Customer 1, Product A

and update the Customer record to something like

UPDATE Customers
SET Customers.UnownedProduct = ProductA
WHERE Customers.CustomerID = Customer1ID

But it would be nice if I could do this whole process, in one SQL statement. So I run the query once, and it updates 1 customer with a product they do not own. Hope that's not too confusing for you! Thanks in advance!

Answer 1

WITH q AS
        (
        SELECT  c.*, p.id AS Unowned,
                ROW_NUMBER() OVER (PARTITION BY p.id ORDER BY c.id) AS rn
        FROM    Customers c
        CROSS JOIN
                Products p
        LEFT JOIN 
                CustomerProducts cp
        ON      cp.customer = c.id
                AND cp.product = p.id
        WHERE   cp.customer IS NULL
        )
UPDATE  q
SET     UnownedProduct = Unowned
WHERE   rn = 1

UPDATE statement will update the first customer who doesn't own a certain product.

If you want to select the list, you'll need:

SELECT  *
FROM    (
        SELECT  c.*, p.id AS Unowned,
                ROW_NUMBER() OVER (PARTITION BY p.id ORDER BY c.id) AS rn
        FROM    Customers c
        CROSS JOIN
                Products p
        LEFT JOIN 
                CustomerProducts cp
        ON      cp.customer = c.id
                AND cp.product = p.id
        WHERE   cp.customer IS NULL
    ) cpo
WHERE   rn = 1

Answer 2

If you update only one customer at once, you might need to remember which products have been assigned automatically (in CustomerProducts) or have a counter how often a product has been assigned automatically (in Products)

Answer 3

I tried this in oracle (hope it works for you too)

UPDATE customers c
   SET unownedProduct =
       ( SELECT MIN( productid )
           FROM products
          WHERE productid NOT IN (
              SELECT unownedProduct
                FROM customers
               WHERE unownedProduct IS NOT NULL )
            AND productid NOT IN (
              SELECT productid
                FROM customerProducts cp
               WHERE cp.customerId = c.customerid )
       )
 WHERE customerId = 1

Answer 4

What if the customer doesn't own more than one product? and how are you going to maintain this field as the data changes? I thinkyou really need to do some more thinking about your data structure as it doesn't make sense to store this information in the customer table.