Reputation: 20390
Lets say I have the following tables:
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)
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!
Upvotes: 3
Views: 1208
Reputation: 96610
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.
Upvotes: 0
Reputation: 55594
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
Upvotes: 0
Reputation: 425693
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
Upvotes: 3
Reputation: 55594
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)
Upvotes: 0