November 20, 2007 at 3:51 pm
I have two large tables, CustomersA and CustomersB. Columns in both tables are equivalent.
I need to find all rows from CustomersA where Company and ZipCode (5 digit) are NOT IN CustomersB.
What kind of JOIN or other syntax could I use to find these? I am having a forgetful day!
Thank you!
November 20, 2007 at 4:40 pm
select a.* from customera a
join customerb b on (a.company+a.zipcode<>b.company+b.zipcode)
November 20, 2007 at 5:03 pm
Thank you very much! I have used that plenty of times but just did not think to use it in this case.
November 20, 2007 at 8:36 pm
select a.* from customera a
join customerb b on (a.company+a.zipcode<>b.company+b.zipcode)
Ummmm... You might wanna go back and take a look at that code...
[font="Courier New"]--===== Setup a test table and populate with test data
 CREATE TABLE CustomersA (Company VARCHAR(5), ZipCode CHAR(5))
 INSERT INTO CustomersA 
        (Company,ZipCode)
 SELECT 'AAA', '11111' UNION ALL
 SELECT 'BBB', '22222' UNION ALL
 SELECT 'CCC', '33333' UNION ALL
 SELECT 'DDD', '44444' UNION ALL
 SELECT 'EEE', '55555'
--===== Setup identical test table and populate with identical data
     -- except for one row
 CREATE TABLE CustomersB (Company VARCHAR(5), ZipCode CHAR(5))
 INSERT INTO CustomersB
        (Company,ZipCode)
 SELECT Company,ZipCode
   FROM TableA
  WHERE Company <> 'CCC'
--===== PostIt's code
select a.* from customersa a
join customersb b on (a.company+a.zipcode<>b.company+b.zipcode)[/font]
Try this, instead...
[font="Courier New"]SELECT a.*
   FROM CustomersA a
   LEFT OUTER JOIN CustomersB b
     ON a.Company = b.Company
    AND a.ZipCode = b.ZipCode
  WHERE b.Company IS NULL[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply