SQLTeam.com | Weblogs | Forums

How to do multiple condition search


#1

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.

  1. Customer #, Invoice #, Serial # and Model #

If there is a match customer records are updated. For those with no match, a 2nd search is done

  1. Customer #, Serial # and Model #

If there is a match customer records are updated. For those with no match, a 3rd search is done

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


#2

Try this:

select a.*
  from tbl2 as a
 where exists (select 1
                 from #tbl1 as b
                      left outer join tbl1 as c
                                   on c.customer_nbr=a.customer_nbr
                                  and c.invoice_nbr=a.invoice_nbr
                                  and c.serial_nbr=a.serial_nbr
                                  and c.model_nbr=a.model_nbr
                      left outer join tbl1 as d
                                   on d.customer_nbr=a.customer_nbr
                                  and d.serial_nbr=a.serial_nbr
                                  and d.model_nbr=a.model_nbr
                where b.customer_nbr=a.customer_nbr
                  and b.serial_nbr=a.serial_nbr
                  and c.customer_nbr is null
                  and d.customer_nbr is null
              )
;