help with SQL query - Webmaster Forums - Webmaster forum for HTML, PHP, ASP, CSS and more

goldfish · 09-29-2005, 07:31 PM

Hi,
I am trying to figure out how to write one more sql query. 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. What i need more specifically is 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).

Also 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).

I think I need to be using the SQL join functions for these, or is there a better way to do this accomplish this?

thks

cristobal · 09-30-2005, 03:40 AM

What you can do is to place all the values of the SQL table1 in an array and then use that to compare with every single on in table2
Then if there is a match print something and when there is not a match print something else.

kayla · 09-30-2005, 04:36 PM

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

Hope this helps with your needs