help with one more SQL query...

i am trying to figure out how to write one more sql query. basically i now have 2 tables, both filled with item numbers and quantities. i want to write queries that will produce what is missing between the two tables. here is what i need more specifically:

a query that looks for item numbers that are in one table and not the other, and vice versa (ie item number 20004 is in our table, but doesn't exist in the other table).

a query that prints out discrepancies between quantities for item numbers that do match up (ie item number 20004 is in both tables, but has a quantity of 10 in one and 20 in the other).

it seems that i should be using the SQL join functions for these? is that right, or is there a better way to do this??yes, for the first task (rows in one table but not the other) you will need two queries, each featuring a LEFT OUTER JOIN, with a test for IS NULL in the WHERE clauseselect table1.itemnumber
from table1
left outer
join table2
on table1.itemnumber
= table2.itemnumber
where table2.itemnumber is null

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

for the second task you will need a simple INNER JOINselect table1.itemnumber
, table1.quantity
, table2.quantity
from table1
inner
join table2
on table1.itemnumber
= table2.itemnumber
where table1.quantity
<> table2.quantity|||Essentially the queryselect table1.itemnumber
from table1 left outer join table2 on table1.itemnumber = table2.itemnumber
where table2.itemnumber is nullis a "set difference", hence can also be achieved by using an "EXCEPT" construction:select itemnumber from table1
EXCEPT ALL
select itemnumber from table2The main difference in the result being that in the former case, duplicates in table1 will be shown (as duplicates) only if they aren't present in table2, while in the latter case duplicates may be shown (but with a smaller repeat count) when they are present in the second table.
Without duplicates in table1, both queries give the same result.
In most situations, the EXCEPT query will be faster, though, especially if the second table is large.|||In most situations, the EXCEPT query will be faster...unless your database system doesn't support that syntax, in which case it will take positively forever ;) :)|||unless your database system doesn't support that syntaxin which case it will probably neither support the OUTER JOIN syntax ...
;)|||um, peter, they all support OUTER JOIN syntax ;)

which database(s) were you thinking of that do support EXCEPT?|||Just thinking of Oracle (up to version 9) which has no FULL OUTER JOIN, but has MINUS.
Maybe there are others as well, no idea.|||but you don't need FULL OUTER JOIN to create an "EXCEPT" query, just LEFT OUTER JOIN|||And which DB systems were you thinking of that do not support EXCEPT?|||many more than you were!! ;) :)|||That would (not) surprise me!
;)|||I call a remote/stored procedure on another server.

It then calls other stored procedure with the EXEC statement.

It seems to me that logically this would get done remotely as well no? But It seems not to be from my tests.

Is there a easy way to specify that the SP - and all it's sub calls get done remotely, or do I need to specifically specify for each of these that they should be run remotely in each and every EXEC statement?