Query Help / List What Isn't Found 1

[ResonantEcho]

Table1:

Column: DebtID
Sample Value: G2010090058868


Table 2:

Column: IndexValue
Sample Value: prom~000000000~a~DCMS02~GRAYLG_0002101985~~~~~11/17/2009~~~0002101985~G2010090058868~~~~~00000129.001


I need to list all the "DebtID"s in "Table 1" that do not exist in "Table 2". Unfortunately, "Table 2" does not have a "DebtID" column. However, the "DebtID" can be found in the "IndexValue" column in "Table 2". Weird, I know. But this is what I have to work with.

I know I can use LIKE or CHARINDEX to find if there is a match.

select indexvalues
from table2 with (nolock)
where charindex('G2010090058868', indexvalues) > 0

However, what is the most efficient query to use (subquery or join) to list ALL the "DebtID"s that do not exist in "Table 2"? Since it's not a simple column to column join I'm not sure how to do this.

Thanks for your help in advance. I'm new to the SQL world. Everything I've tried either hasn't worked or takes way to long to pull back a result. There is an INDEX on both columns mentioned.

 

[bborissov]

SELECT indexvalues
FROM Table2
LEFT JOIN Table1 ON charindex(Table1.DebtID, Tabl2.indexvalues) > 0
WHERE Table1.DebtID IS NULL

Is this one time job?
If NOT I will strongly suggest you to have DebtId column in Table2


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[ResonantEcho]

Thank you, Borislav. I'll give this a shot and get back with you. That is very straight forward and simple. I didn't realize you could us ON with a LIKE or CHARINDEX.

Another quick question. "Table 1" has only 100,000+ rows. "Table 2" has 500+ million. Is this the most efficient JOIN? Would all the rows in "Table 2" be scanned with the way the query is currently written? Or only the rows in the JOINED table?

This will be a one time job. However, I did consider parsing out the DebtID from the IndexValues column and making it a column of its own. The problem is over the years the number of values and their order has changed (values between delimeter "~").

 

[bborissov]

No that is NOT efficient way.
That is why I asked if this is one time job or not.



Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[ResonantEcho]

Borislave,

I guess what I was asking is doing the reverse more efficient? Less row scans?

SELECT    DebtID
FROM      Table1
LEFT JOIN Table2 ON charindex(Table1.DebtID, Table2.indexvalues) > 0
WHERE     Table2.indexvalues IS NULL

100,000+ comparisons compared to 500+ million?

 

[ResonantEcho]

Correction:

100,000+ rows scanned compared to 500+ million?

 

[bborissov]

I doubt that there will be difference.
Maybe with EXISTS can be faster:

SELECT * FROM Table1
WHERE NOT EXISTS(SELECT * 
                        FROM Table2
                  WHERE charindex(Table1.DebtID, Table2.indexvalues) > 0)

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[ResonantEcho]

Borislav,

Gotcha!

One last question. What if I want to narrow down the DebtIDs in Table1? For example. Let's say I want only DebtIDs that start with F, G, or N and are exactly 14 characters long? Where would I add that to the last query you supplied?

I know the syntax to check for those conditions would be:

WHERE SUBSTRING(DebtID, 1, 1) IN ('F', 'G', 'N')
AND   LEN(DebtID) = 14

 

[ResonantEcho]

Never mind, Borislav. I just realized I could probably add those conditions to the original query.

SELECT  DebtID 
FROM    Table1
WHERE   SUBSTRING(DebtID, 1, 1) IN ('F', 'G', 'N')
AND     LEN(DebtID) = 14
AND NOT EXISTS(SELECT IndexValues 
               FROM   Table2
               WHERE  CHARINDEX(Table1.DebtID, Table2.IndexValues) > 0)

I'm not sure if it will trim down the time the query runs but we shall see.

Thanks again for your help. I'll report back with results.