1,105,578 Community Members

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

Member Avatar
haranaboy
Light Poster
34 posts since Sep 2010
Reputation Points: 2 [?]
Q&As Helped to Solve: 5 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
BitBlt
Practically a Posting Shark
895 posts since Feb 2011
Reputation Points: 442 [?]
Q&As Helped to Solve: 163 [?]
Skill Endorsements: 16 [?]
Featured
 
2
 

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!

Member Avatar
simplypixie
Practically a Master Poster
673 posts since Oct 2010
Reputation Points: 123 [?]
Q&As Helped to Solve: 122 [?]
Skill Endorsements: 6 [?]
 
1
 

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

Question Answered as of 2 Years Ago by BitBlt and simplypixie
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: