SQL Logic or Query to find what is missing

Question

Hi all I need your help on the following logic. Currently I have a table that has 300 records, that are related but on this new tables I have the columns called them country, POS so for each combination of country + POs I should have 1 record of table A.

but the situation is that when I am checking the last table someone only inserted some records of table A into table b, and now I have to find what are the missing combination.

could you guide me on the logic that I should use for this, any question please let me know.

Example

Table A name Categories
Milk
Hot Sauces
Meat

Table B
Category    POS    Country
Milk        DF      Mexico
Meat        DF      Mexico
Hot Sauces  DF      Mexico
Milk        CC      Canada

Like you can see Canada still missing 2 categories but this table have all Americas countries so let say I have 20 countries. So 20 multiple by 300 categories I should have 6000 distinct records or more because each country have different quantities of POS, right, but someone only inserted let say 3600 records so now I have to find what combination are missed.

http://stackoverflow.com/questions/4602083/sql-compare-data-from-two-tables — FlavorScape, Sep 28 '16 at 18:23
Thanks Flavor but still not answering my scenario, because Table a and table b on my question have differents amount of columns and records. — RARV, Sep 28 '16 at 18:35
you need a cartisean join between distinct countries and categories then compare against that it is relatively easy just cross join but I am not following what the POS is and how it relates to your desired result. Plus you really should show us what your desired result is! — Matt, Sep 28 '16 at 18:41
MS SQL Server, I know what you mean but for me is complicated to express the idea. POS is Point of Sales, let say that on country like USA you have a Walmart by each state or Mexico have a specific chain of store for each city. — RARV, Sep 28 '16 at 18:46

score 1 · Accepted Answer · answered Sep 28 '16 at 19:34

If you Don't have a country table you can derive one by selecting DISTINCT Country from your TableB. Then cross join that with Categories for a Cartesian Join (all possible combinations) between Countries and Categories.

SELECT countries.country, c.Category
FROM
    (SELECT DISTINCT Country
    FROM
       @TableB) as countries
    CROSS JOIN @Categories c
    LEFT JOIN @TableB b
    ON countries.Country = b.Country
    AND c.Category = b.Cateogry
WHERE
    b.Cateogry IS NULL

If you actually need All Possible Combinations of POS and Country and Categories. In this case it sounds like POS is more like a store than a point of sale but same concept. Just derive a POS table if you don't have one and cross join it with the cross join of countries and categories.

SELECT
    countries.country, c.Category, pos.POS
FROM
    (SELECT DISTINCT Country
    FROM
       @TableB) as countries
    CROSS JOIN @Categories c
    CROSS JOIN (SELECT DISTINCT POS
       FROM
          @TableB) as pos
    LEFT JOIN @TableB b
    ON countries.Country = b.Country
    AND c.Category = b.Cateogry
    AND pos.POS = b.POS
WHERE
    b.Cateogry IS NULL

But I would guess that not every store is in every country so you probably want to constrain the POS combiantions to POS's that are available in a particular country. Again you can derive the table if you don't have one this time include Country and do an inner join between the derived country table and it.

SELECT
    countries.country, c.Category, pos.POS
FROM
    (SELECT DISTINCT Country
    FROM
       @TableB) as countries
    CROSS JOIN @Categories c
    INNER JOIN (SELECT DISTINCT Country, POS
       FROM
          @TableB) as pos
    ON countries.Country = pos.Country
    LEFT JOIN @TableB b
    ON countries.Country = b.Country
    AND c.Category = b.Cateogry
    AND pos.POS = b.POS
WHERE
    b.Cateogry IS NULL

test data used:

DECLARE @Categories AS TABLE (Category VARCHAR(25))
DECLARE @TableB AS TABLE (Cateogry VARCHAR(25),POS CHAR(2), Country VARCHAR(25))
INSERT INTO @Categories VALUES ('Milk'),('Hot Sauces'),('Meat')
INSERT INTO @TableB VALUES ('Milk','DF','Mexico'),('Meat','DF','Mexico'),('Hot Sauces','DF','Mexico'),('Milk','CC','Canada'),('Milk','XX','Canada')

score 0 · Answer 2 · answered Sep 28 '16 at 18:12

0

Hi,
You can use below logic to get missing data,

SELECT column_name FROM tableA WHERE column_name NOT IN 
(SELECT column_name FROM tableB)

Change the required column names and table names in the query. Use same column names in all three places

answered Sep 28 '16 at 18:12

Jim Macaulay

4,709
4
28
53

SQL Logic or Query to find what is missing

2 Answers2