help with one more SQL query...

bonzovt · #1 (**permalink**) 09-26-05, 09:27

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??

r937 · #2 (**permalink**) 09-26-05, 09:57

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 clause

Code:

select 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 JOIN

Code:

select table1.itemnumber
     , table1.quantity
     , table2.quantity
  from table1
inner
  join table2
    on table1.itemnumber
     = table2.itemnumber  
 where table1.quantity
    <> table2.quantity

Peter.Vanroose · #3 (**permalink**) 10-01-05, 15:58

Essentially the query

Code:

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

is a "set difference", hence can also be achieved by using an "EXCEPT" construction:

Code:

select itemnumber from table1
EXCEPT ALL
select itemnumber from table2

The 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.

r937 · #4 (**permalink**) 10-01-05, 16:48

Quote:

Originally Posted by Peter.Vanroose

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

Peter.Vanroose · #5 (**permalink**) 10-02-05, 16:10

Quote:

unless your database system doesn't support that syntax

in which case it will probably neither support the OUTER JOIN syntax ...

r937 · #6 (**permalink**) 10-02-05, 17:10

um, peter, they all support OUTER JOIN syntax

which database(s) were you thinking of that do support EXCEPT?

Peter.Vanroose · #7 (**permalink**) 10-03-05, 01:14

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.

r937 · #8 (**permalink**) 10-03-05, 06:19

but you don't need FULL OUTER JOIN to create an "EXCEPT" query, just LEFT OUTER JOIN

Peter.Vanroose · #9 (**permalink**) 10-03-05, 12:40

And which DB systems were you thinking of that do not support EXCEPT?

r937 · #10 (**permalink**) 10-03-05, 13:18

many more than you were!!

Peter.Vanroose · #11 (**permalink**) 10-03-05, 14:05

That would (not) surprise me!

achilez · #12 (**permalink**) 10-10-05, 05:09

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?

Internet Services

Web Development

Digital Marketing

Consumer Tech