RARV - 2 months ago 9
SQL Question

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
``````

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.

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