CODEWITHSUNDEEP
sqlsql-serverrecords
RARV
RARV

Reputation: 31

SQL Logic or Query to find what is missing

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.

Upvotes: 1

Views: 932

Answers (2)

Matt
Matt

Reputation: 14341

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')

Upvotes: 1

Jim Macaulay
Jim Macaulay

Reputation: 5141


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

Upvotes: 1

Related Questions