Join Query- Help!

Question

Post reply

Join Query- Help!

Gavin Oosthuizen

Old Hand

Points: 315
More actions
September 28, 2006 at 12:37 am

#169836

Good day all,
I need to join 2 tables from different servers to compare a column. The column is called accpaccode, and exists in both tables. What i need to know is how to compare the data in a way that will match the accpaccode in table CustomerBranch to table tbCustBranch, and return the values that exist in CustomerBranch but not in tbCustBranch.
Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ganeshc27 SSC Eights! Points: 922 More actions · Answer 1

Try this...

SELECT A.*

FROM CUSTOMERBRANCH A

WHERE NOT EXISTS (SELECT * FROM [SERVER].[DATABASE].DBO.TBCUSTBRANCH B WHERE A.ACCPACCODE = B.ACCPACCODE)

Regards,

Ganesh

Gavin Oosthuizen Old Hand Points: 315 More actions · Answer 2

Ganesh, thanks a stack! This is exactly what I needed!

John Rowan SSC Guru Points: 56440 More actions · Answer 3

Or use an ANSI OUTER JOIN:

SELECT A.*

FROM CUSTOMERBRANCH A

LEFT JOIN [SERVER].[DATABASE].DBO.TBCUSTBRANCH B

ON A.ACCPACCODE = B.ACCPACCODE

WHERE B.ACCPACCODE IS NULL

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Sergiy SSC Guru Points: 110192 More actions · Answer 4

Only one point:

remote query will copy the whole remote table to local server. No use of index, no way for optimiser to do its job.

So, on big tables this query will be really sloooow.

_____________
Code for TallyGenerator