CODEWITHSUNDEEP
mysql
TychaBrahe
TychaBrahe

Reputation: 49

SQL: easiest way to show missing data

I'm working with a sales tool. We have a table of customers, and we have a table of items. However, a customer is only allowed to purchase a product if there is a record wth that customer and that item in a pricing table. Sometimes there are records missing from this table, and some customers can't order some items.

I have a list of customers (CustA, CustB, CustC) and a list of items (Item1, Item2, Item3). I want to see if pricing table records don't exist for these nine possibilities. I can write a query that will show me where a pricing record exists, but if I only get seven records, I have to manually check each possibility against what I should see to find the missing records.

Select PricingContractID, ItemKey 
from PricingContracts 
where PricingContractID in (CustA, CustB, CustC) 
   and ItemKey in (Item1, Item2, Item3)

Results: 

| CustA | Item1 |   
| CustB | Item1 |   
| CustA | Item3 |   
| CustA | Item2 |   
| CustB | Item3 |  
| CustC | Item2 |  
| CustC | Item3 |

I want to query that will show me which records don't exist. 

| CustB | Item2 |  
| CustC | Item1 |

And especially, I would love to have it in an array with item numbers on one axis, customer numbers on the other, and some indicator that a record exists.

|   •   | Item1 | Item2 | Item3 |  
| CustA |   Y   |   Y   |   Y   |   
| CustB |   Y   |       |   Y   |   
| CustC |       |   Y   |   Y   |

Upvotes: 0

Views: 836

Answers (1)

Progman
Progman

Reputation: 19546

First you build a query to select all possible combinations, then you run a LEFT JOIN on the link table. When you have NULL values you know there is no such row in the link table and can read the column values.

The query without the JOIN on the link table:

SELECT
    item.name,
    customer.name
FROM
    item
JOIN
    customer;

+------+------+
| name | name |
+------+------+
| a1   | b4   |
| a2   | b4   |
| a3   | b4   |
| a1   | b5   |
| a2   | b5   |
| a3   | b5   |
| a1   | b6   |
| a2   | b6   |
| a3   | b6   |
+------+------+

Then add the JOIN on the link table:

SELECT
    item.name,
    customer.name,
    link.itemName,
    link.customerName
FROM
    item
JOIN
    customer
LEFT JOIN
    link ON item.name = link.itemName AND
            customer.name = link.customerName;

+------+------+----------+--------------+
| name | name | itemName | customerName |
+------+------+----------+--------------+
| a1   | b5   | a1       | b5           |
| a2   | b5   | a2       | b5           |
| a2   | b6   | a2       | b6           |
| a3   | b4   | a3       | b4           |
| a1   | b4   | NULL     | NULL         |
| a2   | b4   | NULL     | NULL         |
| a3   | b5   | NULL     | NULL         |
| a1   | b6   | NULL     | NULL         |
| a3   | b6   | NULL     | NULL         |
+------+------+----------+--------------+

And then simply filter the rows you want:

SELECT
    item.name,
    customer.name,
    link.itemName,
    link.customerName
FROM
    item
JOIN
    customer
LEFT JOIN
    link ON item.name = link.itemName AND
            customer.name = link.customerName
WHERE
    link.itemName IS NULL;

+------+------+----------+--------------+
| name | name | itemName | customerName |
+------+------+----------+--------------+
| a1   | b4   | NULL     | NULL         |
| a2   | b4   | NULL     | NULL         |
| a3   | b5   | NULL     | NULL         |
| a1   | b6   | NULL     | NULL         |
| a3   | b6   | NULL     | NULL         |
+------+------+----------+--------------+

Upvotes: 1

Related Questions