0

I have 2 tables as below I column is common to both. Table 2 has FK from Table 1. enter image description here

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

  1. List all BoxNo and subtract the count if the ID_Tag appears in table 2.
  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.

closed as unclear what you're asking by Gaius, Lennart, hot2use, Marco, SqlWorldWide May 7 '18 at 12:36

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

2

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                |

Not the answer you're looking for? Browse other questions tagged or ask your own question.