compare columns in 2 tables

Hi all,

I have 2 tables with below sample data. Both have same columns code1,code2,name desc. I need to compare all the  columns from table 2 to table 1. If all 4 or 3 or 2 or 1 fields match I should remove that row from table 2 and create result table with additional column called match(create table happens for first row with either partial or full match only, later rows are appended to same table). If all 4 fields are matched the match is defined as max, if 3 columns match it is most, if 2 columns match medium and for 1 column match it is least. If no match that row should be left in table 2 itself.

Table1
----------
Code1       code2      Name                    desc
12               45            soap1                    johnsonandjohnson
34                67             juice1                   welch
454             298            brushmodel1           palmolive
456               876       shoes1                    nike

 Table2
----------
Code1       code2      Name                   desc
12             45             soap1                   johnsonandjohnson
34            67            juice1                 Tropicana
454            298            brushmodel2      sonicare
456            665            shoesmodel2      adidas,20% discount
178            672            iphone 5                apple device
12            76            soap1                    johnsonandjohnson
34            45            soap1                   Dove
89            45            babyshampoo      johnsonandjohnson


Result Table:
-------------------
Code1       code2      Name                    desc                                         match
12                   45      soap1                    johnsonandjohnson          max
34                  67      juice1                    Tropicana                                most
454                 298      brushmodel2      sonicare                                   medium
456                665      shoesmodel2      adidas,20% discount            least
12                  76      soap1                         johnsonandjohnson        least
34                  45      soap1                             Dove                               least
89                    45      babyshampoo      johnsonandjohnson           medium

Table2(after match)
----------
Code1       code2      Name                   desc
178            672            iphone 5                apple device

Should this be written with case statement in where clause? Can somebody guide me how to go about this?
JyozealAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Thunder724Commented:
You could do this several ways....
Create your "Match" table.
I added an additional column so that you set the number of successful comparisons.  If you have a primary key on table1 or table2 you may want to add that to the MATCH table as well.

This below example uses four queries, you could also use a CASE Statement too.... (Just an example)

/*ALL FOUR MATCH*/
INSERT INTO MATCH(CODE1, CODE2, NAME, DESC, MATCH_TYPE)
(SELECT 
	T2.CODE1
	,T2.CODE2
	,T2.NAME
	,T2.DESC
	,4
FROM TABLE2 T2
	INNER JOIN TABLE1 T1 ON(T1.CODE1 = T2.CODE1 AND T1.CODE2 = T2.CODE2 AND T1.NAME = T2.NAME AND T1.DESC = T2.DESC))


/*ONLY THREE MATCH*/
INSERT INTO MATCH(CODE1, CODE2, NAME, DESC)
(SELECT 
	T2.CODE1
	,T2.CODE2
	,T2.NAME
	,T2.DESC
	,3
FROM TABLE2 T2
	INNER JOIN TABLE1 T1 ON(T1.CODE1 = T2.CODE1 AND T1.CODE2 = T2.CODE2 AND T1.NAME = T2.NAME AND T1.DESC <> T2.DESC))

/*ONLY TWO MATCH*/
INSERT INTO MATCH(CODE1, CODE2, NAME, DESC)
(SELECT 
	T2.CODE1
	,T2.CODE2
	,T2.NAME
	,T2.DESC
	,2
FROM TABLE2 T2
	INNER JOIN TABLE1 T1 ON(T1.CODE1 = T2.CODE1 AND T1.CODE2 = T2.CODE2 AND T1.NAME <> T2.NAME AND T1.DESC <> T2.DESC))

	
/*ONLY ONE MATCH*/
INSERT INTO MATCH(CODE1, CODE2, NAME, DESC)
(SELECT 
	T2.CODE1
	,T2.CODE2
	,T2.NAME
	,T2.DESC
	,1
FROM TABLE2 T2
	INNER JOIN TABLE1 T1 ON(T1.CODE1 = T2.CODE1 AND T1.CODE2 <> T2.CODE2 AND T1.NAME <> T2.NAME AND T1.DESC <> T2.DESC))

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
>> Should this be written with case statement in where clause? <<
Roughly, yes.
>> Can somebody guide me how to go about this? <<
Yes ... see below :-).

IF OBJECT_ID('tempdb.dbo.#matches') IS NOT NULL
    DROP TABLE #matches

SELECT TOP (0) t2.Code1, t2.Code2, t2.Name, t2.[Desc]
INTO #matches
FROM dbo.Table2 t2
UNION ALL
SELECT TOP (0) t2.Code1, t2.Code2, t2.Name, t2.[Desc]
FROM dbo.Table2 t2

BEGIN TRY
BEGIN TRANSACTION

INSERT INTO Result_Table ( Code1, Code2, Name, [Desc], Match )
OUTPUT t2.Code1, t2.Code2, t2.Name, t2.[desc] INTO #matches
SELECT
    t2.Code1, t2.Code2, t2.Name, t2.[desc],
    CASE
        CASE WHEN t1.Code1  = t2.Code1  THEN 1 ELSE 0 END +
        CASE WHEN t1.Code2  = t2.Code2  THEN 1 ELSE 0 END +
        CASE WHEN t1.Name   = t2.Name   THEN 1 ELSE 0 END +
        CASE WHEN t1.[desc]   = t2.[desc]   THEN 1 ELSE 0 END
        WHEN 4 THEN 'Max'
        WHEN 3 THEN 'Most'
        WHEN 2 THEN 'Medium'
        ELSE 'Least' END AS match
FROM dbo.Table1 t1
LEFT OUTER JOIN dbo.Table2 t2 ON
    (t1.Code1 = t2.Code1 OR t1.Code2 = t.Code2 OR t1.Name = t2.Name OR t1.[desc] = t2.[desc])

DELETE
FROM t2
FROM dbo.Table2 t2
INNER JOIN #matches m ON
    m.Code1 = t2.Code1 AND
    m.Code2 = t2.Code2 AND
    m.Name = t2.Name AND
    m.[Desc] = t2.[Desc]

COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
    ROLLBACK TRANSACTION
SELECT ERROR_LINE() AS error_line, ERROR_MESSAGE() AS error_message
END CATCH
JyozealAuthor Commented:
Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.