I want to compare Customer #, Invoice #, Serial # and Model # in two tables, to check if these four fields match.
It usually takes three round searches if done in Excel Spreadsheets.
- Customer #, Invoice #, Serial # and Model #
If there is a match customer records are updated. For those with no match, a 2nd search is done
- Customer #, Serial # and Model #
If there is a match customer records are updated. For those with no match, a 3rd search is done
- Customer # and Serial #
This is the search that provides inaccurate matches because we only have the last 6 digits of the serial number. These are the items we are looking for. The units matched this way need to be provided to the business so they can review and correct where necessary.
Anyone can give me a logic to do it in SQL? Thank you!
I add some values to the tables.
CREATE TABLE tbl1
(
Customer_Nbr VARCHAR(255),
Invoice_Nbr VARCHAR(255),
Serial_Nbr VARCHAR(255),
Model_Nbr VARCHAR(255)
)
INSERT INTO tbl1
VALUES
('007849', '11613577', '100000','92002100'),
('007849', '11613577', '100200','92005400'),
('007850', '11613578', '187111', '92104000'),
('007850', '11613578', '187222', '92104000'),
('007850', '11613582', '187654', '92005400'),
('007851', '11613579', '123145', '92002100'),
('007852', '11613580', '225146', '92002100')
CREATE TABLE tbl2
(
Customer_Nbr VARCHAR(255),
Invoice_Nbr VARCHAR(255),
Serial_Nbr VARCHAR(255),
Model_Nbr VARCHAR(255)
)
INSERT INTO tbl2
VALUES
('007849', '11613577', '100000','92002100'),
('007849', '11613579', '100200','92005400'),
('007850', '11613578', '187111', '92104000'),
('007850', '11613578', '187222', '92104000'),
('007850', '11613588', '187654', '92005401'),
('007851', '11613579', '123145', '92002100'),
('007852', '11613580', '225146', '92002100')
The objective is to to returns
('007850', '11613588', '187654', '92005401'), because this item only has customer# and serial# match with table1, while model# and invoice# not match with table1.
What should I do in sql?