Compair Two tables and return result in column

Sachin Pimpale

Hi Experts I am in great confusion :confused: can you help me to overcome I am having 2 table "TABLE_A" and "TABLE_B" bot have same fields "Field_1","Field_2". I want to return a result in such a way that compairing both tables with their fields, and return result as "Match" or "Unmatch" in new column ex: TABLE_A.Field_1 TABLE_A.Field_2 TABLE_B.Field_1 TABLE_B.Field_2 RESULT ABC XYZ ABC XYZ Match PQR XYZ ABC XYZ Unmatch LOM XYZ LOM XYZ Match LOM KKR LOM KKR Match UER SQE OER KKR Unmatch

The Stifler -- Bugs can neither be created nor be removed from software by a developer. They can only be converted from one form to another. The total number of bugs in the software always remain constant.

Tripathi Swati

By assuming your field is of varchar...

case when
(TABLE_A.Field_1 + TABLE_A.Field_2) = (TABLE_B.Field_1 + TABLE_B.Field_2)
then 'Match'
else 'Unmatch' end

i guess this will help you .

Reasons are not Important but Results are Important. http://www.sql4professional.blogspot.com Swati Tripathi

Sachin Pimpale

hi again I found following query SELECT T1.C1,T1.C2, CASE WHEN (SELECT COUNT(*) FROM TABLE_2 T2 WHERE T1.C1 = T2.C1 AND T1.C2 = T2.C2 ) = 0 THEN 'Unmatch' ELSE 'Match' END AS Result FROM TABLE_1 T1 Still :confused::confused::confused: i am not able to retrieve fields from Table_2 which are not present in Table_1

The Stifler -- Bugs can neither be created nor be removed from software by a developer. They can only be converted from one form to another. The total number of bugs in the software always remain constant.

J4amieC

You have 2 options here. Assume one of the two tables is the "master" and left join the other:

select t1.field1,t1.field2,
t2.field1,t2.field2,
CASE
WHEN t2.field1 IS NOT NULL THEN 'Match'
ELSE 'Unmatch'
END
FROM Table1 t1
LEFT JOIN table2 t2
ON t1.field1=t2.field1 AND t1.field2=t2.field2

or, cross join all of t1 against t2 to find matches.

select t1.field1,t1.field2,
t2.field1,t2.field2,
CASE
WHEN t1.field1=t2.field1 AND t1.field2=t2.field2 THEN 'Match'
ELSE 'Unmatch'
END
FROM Table1 t1
CROSS JOIN table2 t2

Im sure one of those 2 approaches will give you the result you're after.

Tripathi Swati

HI pls try below code

Select
C1,
C2,
C3,
C4,
case when (C1 + C2) = (C3 + C4) then 'Match'
else 'Unmatch' end as Result
From
(

Select 
Row,
Max(C1) as C1,
Max(C2) as C2,
Max(C3) as C3 ,
Max(C4) as C4
From 
(
			Select 
			ROW\_NUMBER() 
					OVER (ORDER BY TABLE\_A.Field\_1) AS Row,
			TABLE\_A.Field\_1  as C1
			,TABLE\_A.Field\_2  as C2
			,'0' as C3
			,'0' as C4
			From 
			TABLE\_A

			Union ALL

			Select 
			ROW\_NUMBER() 
					OVER (ORDER BY TABLE\_B.Field\_1) AS Row,
			'0'  as C1
			,'0' as C2
			,TABLE\_B.Field\_1  as C3
			,TABLE\_B.Field\_2  as C4
			From 
			TABLE\_B
  )
  as OuterTable
  group by Row

) as FinalTable

n yes let me know which sql version u r using because in sql 2008 we have merge statement too... i hope this will help you ..

Reasons are not Important but Results are Important. http://www.sql4professional.blogspot.com Swati Tripathi