SQL query to count with subtraction from count of other sql table I have 2 tables as below I column is common to both. Table 2 has FK from Table 1.


I need to get the count of ID_Tag per BoxNo as result. Condition


List all BoxNo and subtract the count if the ID_Tag appears in table 2.
If BoxNo has 0 tags after subtraction do not display in the result


SQL Query I have tried

 Select tb1.BoxNo, 
        Count(tb1.ID_Tag) - Count(tb2.DelID_Tag) As NumberOfTags 
 From Table1 tb1 
   Left Outer Join Table2 tb2 on tb1.ID_Tag = Tb2.DelId_Tag 
 Group By tb1.BoxNo


I get the list of all the BoxNo with the count from Table1.
This works for your sample data, but may need further testing (and possible adjustments) against a larger set of data.

--Demo setup
declare @Table1 table (Id_tag int, BoxNo varchar(5))
declare @Table2 table (DeleteID_tag int, Col1 varchar(5))

insert into @Table1(Id_tag,BoxNo) values
(5699,'A4'),
(5538,'A4'),
(4567,'A5'),
(2222,'A3'),
(1234,'A4');
Insert into @Table2(DeleteID_tag,Col1) values
(1234,'abc'),
(2222,'jkl')
;

--The actual query
SELECT t1.BoxNo
    ,count(*)
FROM @Table1 t1
WHERE NOT EXISTS (
        SELECT *
        FROM @Table2
        WHERE DeleteID_tag = t1.Id_tag
        )
GROUP BY t1.BoxNo




| BoxNo | (No column name) |
|-------|------------------|
| A4    | 2                |
| A5    | 1                |