select from tableA where key not found in tableB

[josel]

I am truly facing the age old problem of a seasoned programmer in an application trying to do things in an unknown environment.

This I do day in and day out in a DB application I've worked with for years, but as a CF newbie, I can't even imagine ...

Here is the question:
What would a query look like if I needed to EXCLUDE records which are found in a second table

<cfquery ...>
select *
from tableA
where tableA.key NOT FOUND IN tableB
</cfquery>

Do I need a mentallity adjustment? As an inmigrant, I am always told: &quot;Do not think in your native language!&quot; this causes you to speak improper English ... Well, it is hard to do and here I am doing it in &quot;programming&quot; languages as well

Thank you all in advance;

josel If you have the knowledge, consult and educate those who need it! - Jose Lerebours

 

[Kenvase]

Josel, This might do it.
select *
from tableA
where tableA.key NOT IN (select tableB.key from tableB
where tableB.key LIKE '%')

/Kent H

 

[Guest_imported]

Kenvase's query looks like it will work and is the way most of us would first try to solve the problem. but, it is very slow.

Try this and compare the execution times (turn on debugging and report back to us the difference):

SELECT *
FROM    tableA LEFT OUTER JOIN tableB ON tableA.key = tableB.key
WHERE  tableB.key IS NULL

When you use this inside a CFQUERY tag, use this syntax:

<cfquery name=&quot;yadda&quot; datasource=&quot;#my_datasource#&quot;>
  select *
  from {oj  tableA LEFT OUTER JOIN tableB ON tableA.key = tableB.key}
  where tableB.key IS NULL
</cfquery>[code]