text field comparison 1

[KatherineF]

I have 2 tables:
table1
PartnerID PartnerName
table2
ID Name

How to return only records from table2.Name that are not in table1.

Thank you!

 

[amorous]

Try something like this:

SELECT * FROM table2 WHERE ID NOT EXISTS IN (SELECT PartnerID FROM table1)

-VJ

 

[MapMan]

Hi Katherine,

You could try:

[COLOR=blue]Select[/color] PartnerID, PartnerName [COLOR=blue]from[/color] Table1
  [COLOR=fuchsia]left[/color] [COLOR=gray]join[/color] Table2 [COLOR=blue]on[/color] PartnerID = ID
  [COLOR=blue]Where[/color] ID [COLOR=blue]Is[/color] [COLOR=gray]Null[/color]

MapMan

Assume nothing, question everything, be explicit not implicit, and you'll always be covered.

 

[amorous]

MapMan,

You got it reversed.

Katherine is looking for something like this

Select ID, Name from Table2
left join Table1 on ID = PartnerID
Where PartnerID Is Null


-VJ

 

[MapMan]

Thanks amorous...

I kept looking at it before sending it. I even tried it with some of my tables.

Thank you for correcting my.

MapMan

Assume nothing, question everything, be explicit not implicit, and you'll always be covered.

 

[KatherineF]

thank you, but ID is not a foreign key to PartnerID

 

[KatherineF]

let me give you a full picture:

I received an excel file with 2 columns:

1 column contains name of potential dealers
2 column contains phone numbers

I should give them back the list of names with phone numbers that are not in the database.

So I created temp table in the database (temp_dealers) and copied names and phone numbers from excel file to it.

In the database I have table Partner that contains Partner Names and I have other table called Contact that contains phone numbers.

 

[SQLSister]

if Id is not related to partner Id how are the two tables related? If there is no relationship, then the task cannot be done becasue the data are not the same.

example If I have two tables, cusatomer and supplier and customer has an id and supplier has an ID but there is no relted fiedl, then I can't tell what customers are not also suppliers because there is no data to link the two tables togetrher.

INcidentally, the left join is preferable to the not exists structure as it is more efficient.

 

[MapMan]

So I created temp table in the database (temp_dealers) and copied names and phone numbers from excel file to it.

Does this mean that Name relates to PartnerName? If so, you can use the same method just substitute ID & PartnerID with Name and PartnerName. But, if the PartnerName and Name are spelled differently in anyway, other than case, there won't be a match.

MapMan

Assume nothing, question everything, be explicit not implicit, and you'll always be covered.

 

[KatherineF]

thank you!