Query which displays a table along with indication

Question

I am attempting to take a list of barcode numbers in one table, and determine which ones exist in another table. As well, groups of barcode numbers correspond to a box and I also need to know which boxes fully match and can be disposed of. Unfortunately the barcode numbers sometimes have extraneous non-numeric characters so I'm using the REPLACE function on both sides of the inner join 'ON' statement. The query below works fine to determine which items in table1 exist in table2. (DOCINDEX245 is the 'bar code' column on table2)

SELECT DISTINCT [BAR CODE]
        ,[Box]
  FROM Table1 ft
  INNER JOIN Table2 doc
  ON REPLACE(REPLACE(ft.[BAR CODE],'M',''),'-WDRN','') = REPLACE(doc.DOCINDEX245,'M','')
  where REPLACE(REPLACE(ft.[BAR CODE],'M',''),'-WDRN','') = REPLACE(doc.DOCINDEX245,'M','')

Where I run into issues is if I try to find the rows in Table1 which don't exist in Table2.

I tried this:

SELECT DISTINCT [BAR CODE]
            ,[Box]
      FROM Table1 ft
      LEFT JOIN Table2 doc
      ON REPLACE(REPLACE(ft.[BAR CODE],'M',''),'-WDRN','') = REPLACE(doc.DOCINDEX245,'M','')
      where REPLACE(doc.DOCINDEX245,'M','') is null

But it ran for 20 minutes with no results before I cancelled it.

Note, Table1 is 10 columns by 60,000 rows, Table2 is 300 columns by 1.4 million rows.

There is an index on DOCINDEX245 which I reorganized but it didn't seem to do anything, or not enough anyway.

This all said, as mentioned at the top I then need to determine which boxes are 'complete' and which have some unmatched rows and which rows those are.

So my ideal results would be something like:

BARCODE, BOX, MATCH
12345,box1,yes
12346,box1,yes
12347,box1,yes
12348,box2,yes
12349,box2,no
12350,box2,yes

etc

I was going to try to create that manually in Excel with the results of the two queries above, but if there is a single query which could do that I am all ears.

So I guess I have multiple issues here, so I'm open to all suggestions.

Thanks

Answer 1

Try Below it will give you all the records present in table1, table1 records present in table2, table2 records not present in table 1. If this is what you want:

SELECT DISTINCT [BAR CODE]
            ,[Box]
      FROM Table1 ft
      FULL OUTER JOIN Table2 doc
      ON REPLACE(REPLACE(ft.[BAR CODE],'M',''),'-WDRN','') = REPLACE(doc.DOCINDEX245,'M','')
      where REPLACE(doc.DOCINDEX245,'M','') is null AND ft.[BAR CODE] IS NOT NULL

Please share some input sample data, if your requirement is different.

Answer 2

You are correct that the index won't help. That is due to the use of the function, in this case REPLACE, in the join condition. That precludes the use of the index. Ideally you would use the form:

ON ft.[BAR CODE] = doc.DOCINDEX245

To do this you may need to use a persisent computed column to remove the extraneous characters. See MS documentation for this (SQL 2008) at https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-computed-column-definition-transact-sql

Once that column is defined put an index on it, and use it in your join condition on the Table1 side of the join.

Answer 3

When your Inner Join works ok, you can Left Join it back to Table1:

with cte as
 ( SELECT DISTINCT [BAR CODE]
        ,[Box]
   FROM Table1 ft
   INNER JOIN Table2 doc
     ON REPLACE(REPLACE(ft.[BAR CODE],'M',''),'-WDRN','') = REPLACE(doc.DOCINDEX245,'M','')
 )
select ft.*,
   case when cte.[BAR CODE] is null then 'no' else 'yes' end as match
FROM Table1 ft
LEFT JOIN cte
  ON ft.[BAR CODE] = cte.[BAR CODE]
 AND ft.[Box] = cte.[Box]

Answer 4

Try this

SELECT [BAR CODE], [Box], 'yes' as match
  FROM Table1 ft
 INNER JOIN Table2 doc
    ON REPLACE(REPLACE(ft.[BAR CODE],'M',''),'-WDRN','') = REPLACE(doc.DOCINDEX245,'M','')
union
SELECT [BAR CODE], [Box], 'no'
  FROM Table1 ft
  LEFT JOIN Table2 doc
    ON REPLACE(REPLACE(ft.[BAR CODE],'M',''),'-WDRN','') = REPLACE(doc.DOCINDEX245,'M','')
 WHERE doc.DOCINDEX245 is null;