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

Question

Suppose I have the following tables:

• Customers
• Products
• CustomerProducts

Is there a way to make a choice from the Customers and Products tables where the values ​​are NOT in the map table? Basically, I need an appropriate list of customers and products that they do NOT belong to.

Another twist: I need to connect one client to the product. So, if 5 clients do not have product A, only the first client in the request should have product A. Thus, the results look something like this:

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

• Customer 1, product A
• Customer 2, product C
• Customer 3, Product D

: UPDATE SQL Sever. :

1, A

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

, SQL. , 1 , . , ! !

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 , .

, :

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

, , ( CustomerProducts) , ( )

Answer 3

I tried this in oracle (hope this 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 does not own more than one product? and how are you going to maintain this field when changing data? I think you really need to think a little about your data structure, since there is no point in storing this information in a client table.