Add Serial# from initial item listing table to counted item table w/out Serial# in SQL

Question

I am working on a small inventory project and I have two tables that I want to merge a specific way.

Here are my two tables:

Table 1 (Initial Item Listing):

   Scan#      ItemNUmber   Serial#
----------- ----------- ---------------
  374         123458      10
  374         123458      11
  374         123458      30

Table 2 (Counted Product):

   Scan#      ItemNumber   Barcode#
----------- ----------- ---------------
  374         123458      926373700243
  374         123458      926373700267

Here is the code I am using:

SELECT DISTINCT * 
FROM (SELECT * 
      FROM 
        (SELECT ScannedItems.CountTagId, ScannedItems.ItemNumber, ScannedItems.barcode
         FROM ScannedItems 

         UNION ALL 

         SELECT CountId, ItemNumber, SerialNumber 
         FROM FrozenData
        ) Tbls
      GROUP BY ItemNumber, CountTagId, Barcode
      HAVING COUNT(*)<2
     ) Diff

Here is the Output:

   Scan#      ItemNUmber   Serial#
----------- ----------- ---------------
  374         123458      10
  374         123458      11
  374         123458      30
  374         123458      926373700243
  374         123458      926373700267

Here is what I want it to Output (Exclude Serial# 30 because it wasn't counted):


 Scan#      ProductNo     Barcode#       SN#
----------- ----------- --------------- -----
  374         123458      926373700243    10
  374         123458      926373700267    11

I know I am forgetting something. Any assistance or a link to somewhere that can assist is greatly appreciated.

Are you intending to rely on the order listed and match up first row with first rows, second with second, etc.? — shawnt00, Sep 06 '19 at 20:41

score 1 · Accepted Answer · answered Sep 06 '19 at 20:51

If you want to match the rows by sequential number, then you will need to add that in:

select si.*, fi.barcode
from (select si.*,
             row_number() over (partition by scan, itemnumber order by serial) as seqnum
      from ScannedItems si
     ) si join
     (select fi.*,
             row_number() over (partition by scan, itemnumber order by barcode) as seqnum
      from FrozenItems fi
     ) fi
     on fi.scan = si.scan and
        fi.itemnumber = si.itemnumber and
        fi.seqnum = si.seqnum;

SQL table represent unordered sets. If the ordering is provided by the third column, this will work for you.

score 0 · Answer 2 · answered Sep 06 '19 at 20:29

Doing a Unino All is stacking the tables on top of each other which is why would getting the extra rows. Secondly, since the scan# and ProductNo are the same how are you to know that the SN# of 30 was the one that was not scanned?

But you could do this as:

Select t1.scan#, t1.ItemNumber, t2.Barcode#, t1.Serial#
from table1 t1
join table2 t2
on t1.scan# = t2.scan#
and t1.ItemNumber = t2.ItemNumber

Add Serial# from initial item listing table to counted item table w/out Serial# in SQL

2 Answers2