Sql Query to Show Missing Records from Another Table

SQL Query to find missing rows between two related tables

SELECT A.ABC_ID, A.VAL FROM A WHERE NOT EXISTS 
   (SELECT * FROM B WHERE B.ABC_ID = A.ABC_ID AND B.VAL = A.VAL)

or

SELECT A.ABC_ID, A.VAL FROM A WHERE VAL NOT IN 
    (SELECT VAL FROM B WHERE B.ABC_ID = A.ABC_ID)

or

SELECT A.ABC_ID, A.VAL LEFT OUTER JOIN B 
    ON A.ABC_ID = B.ABC_ID AND A.VAL = B.VAL FROM A WHERE B.VAL IS NULL

Please note that these queries do not require that ABC_ID be in table B at all. I think that does what you want.

SQL query to show missing records from another table

You can use a left join

select a.name, coalesce(b.quantity, 0) as  quantity
from Customers  a 
left join Events  b on a.name = b.name 

Find missing records with grouping

cross join calendar to distinct items and left join the items table to get the missing rows.

select i.itemid,c.month_last_day
from calendar c 
cross join (select distinct itemid from items) i
left join items it on it.itemid = i.itemid and c.month_last_day = it.dt
where it.dt is null 

SQL Server: How to select missing rows in table from another table?

You need to get a list of all skus and tables, and then show only rows which do not appear in table1:

select SKU, StoreID
from @table2 t2
cross join (select distinct sku from @table1) t1
where not exists (select 1 from @table1 table1 
                  where table1.SKU = t1.SKU 
                    and table1.StoreId = t2.StoreId)

INSERT only missing records from a temporary table

Instead of a join you can use NOT EXISTS e.g.

INSERT INTO Cities (ID_City, Name_City)
    SELECT IDCity, NameCity
    FROM #tCities A
    WHERE NOT EXISTS (SELECT 1 from Cities C WHERE C.ID_City = A.IDCity);

Compare and get missing values from two columns of two different tables

SELECT * FROM StationUtilization
LEFT JOIN  Process
ON Process.TestDateTime = StationUtilization.TestStart
WHERE PROCESS.ID is null

---

Related Topics