Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2004
    Posts
    8

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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    unless your database system doesn't support that syntax
    in which case it will probably neither support the OUTER JOIN syntax ...
    Last edited by Peter.Vanroose; 10-02-05 at 17:12.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    um, peter, they all support OUTER JOIN syntax

    which database(s) were you thinking of that do support EXCEPT?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    but you don't need FULL OUTER JOIN to create an "EXCEPT" query, just LEFT OUTER JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    And which DB systems were you thinking of that do not support EXCEPT?
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    many more than you were!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    That would (not) surprise me!
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  12. #12
    Join Date
    Oct 2005
    Location
    Cebu, Philippines
    Posts
    3

    stored procedure

    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •