selecting a field that doesn't exist in the other table.

[haranaboy]

Hi,

I'm wondering if anyone can help me develop the right sql query.

I'm trying to select a "customerNumber" in table 1, but only if it doesn't exist in table2.

I tried this

Select Table1.customerNumber From Table1, table2
Where table1.customerNumber NOT IN (select table2.customerNumber from table2)

It isn't right because it displays customerNumber that exist in table2.
Can someone please help me?

Thanks

[BitBlt]

I tested your code, and it didn't give me customer numbers that I wasn't expecting. However, in your FROM clause, there is nothing relating table1 to table2. This gives you a cross-join (a.k.a. Cartesian Product) when you execute it. A corrected version of this would be:

select table1.customerNumber from table1
where table1.customerNumber not in (select table2.customerNumber from table2)

This gives a correct result.

An alternative method for doing this type of result retrieval uses a LEFT JOIN like so:

select table1.customerNumber 
from table1 
left join table2
on table1.customerNumber = table2.customerNumber
where table2.customerNumber is null

Depending on the size of the tables and the indices on each table, it could give you superior performance.

Hope this helps! Good luck!

[simplypixie]

You simply need

Select customerNumber From Table1
Where customerNumber NOT IN (select customerNumber from table2)

You don't need to join or select from 2 tables in the main part of your query