Need to compare each rows in two tables

dcs · #1 (**permalink**) 11-16-11, 12:10

Hi all,
I need to compare two tables in each row. the tables are as follows:-

Table a:

Code:

ID	First_Name       Last_name	                       Birthdate
1	Shradha	        Deshkmukh	                    1981-12-25 00:00:00
2	Shradha	             Verma	                    1981-05-11 00:00:00
3	Divya	              Dutta	                    1982-07-21 00:00:00
4	Matthew	              Palmer	                     1983-12-28 00:00:00

table d:-

Code:

id	fn	ln	dob
1	Shradha	Tiwari	1981-12-25 00:00:00
2	Divya	Dutta	1983-07-21 00:00:00
3	Sulabh	Manesar	1975-09-11 00:00:00
4	Matthew	Palmer	1983-12-28 00:00:00
5	Samuel	Maxwell	1984-05-22 00:00:00

I want to compare the tables using first name, and I have a log variable which I want to have the value as per the differences in the table that is if the first name matches and second name and dob dont match it shows log value for that FN as 'LN and DOB dont match'.
similarly if First name matches and dob matches then @log is 'LN not match'.

And in case all three match it should show 'match'as log value.The query I use is a s follows:-

Code:

USE testing
GO

DECLARE @NR int
DECLARE @log varchar(200)
SELECT @NR = COUNT(*) FROM a

WHILE @NR>0 
BEGIN
 
 IF EXISTS(SELECT * FROM a  
 JoIN dbo.d ON a2.fn = d.First_Name
 WHERE a.fn= d.First_Name
 AND a.ln=d.Last_name
 and a.dob=d.Birthdate)
 SET @log =
 'match'
 BREAK;
 
IF EXISTS(SELECT * FROM a  
 JoIN dbo.d ON a.fn = d.First_Name
 WHERE a.fn= d.First_Name
 and a.dob=d.Birthdate
 AND a.ln<>d.Last_name)
 SET @log = 'LN not match' 
 CONTINUE;
 
 IF EXISTS(SELECT * FROM a  
 JoIN dbo.d ON a.fn = d.First_Name
 WHERE a.fn= d.First_Name
 AND a.ln<>d.Last_name
 AND a.dob<>d.Birthdate)
 SET @log ='LN and DOB not match'
 BREAK;
 SET @NR=@NR-1
END;

SELECT a.fn,a.ln,a.dob, @log from dbo.a JoIN dbo.d
ON a.fn = d.First_Name

the result I am getting is :-

Code:

fn	ln	dob	                   (No column name)
Shradha	Tiwari	1981-12-25 00:00:00	match
Shradha	Tiwari	1981-12-25 00:00:00	match
Divya	Dutta	1983-07-21 00:00:00	match
Matthew	Palmer	1983-12-28 00:00:00	match

I have tried using CASE but that doesnt work either.

Now this is something very basic that is wrong in my query but I am a newbie and any help is very much appreciated.

Thanks in advance,
DCS

Wim · #2 (**permalink**) 11-16-11, 13:18

I don't have MSSQL at hand, but this should work:

Code:

SELECT a.fn, a.ln, a.dbo, 
	CASE WHEN a.ln = d.Last_name AND a.dob = d.Birthdate
			THEN 'match'
		WHEN a.ln = d.Last_name AND a.dob <> d.Birthdate
			THEN 'DOB not match'
		WHEN a.ln <> d.Last_name AND a.dob = d.Birthdate
			THEN 'LN not match'
		ELSE -- a.ln <> d.Last_name AND a.dob <> d.Birthdate
			'LN and DOB not match'
	END AS log
FROM a  
	INNER JoIN dbo.d ON 
		a.fn = d.First_Name

dcs · #3 (**permalink**) 11-17-11, 11:45

I need to compare two tables in each row. the tables are as follows:-

Table a:

Code:

ID  First_Name       Last_name               Birthdate
1   Shradha          Deshkmukh               1981-12-25 00:00:00
2   Shradha          Verma                   1981-05-11 00:00:00
3   Divya            Dutta                   1982-07-21 00:00:00
4   Matthew          Palmer                  1983-12-28 00:00:00

table d:-

Code:

 id fn      ln      dob
 1  Shradha Tiwari  1981-12-25 00:00:00
 2  Divya   Dutta   1983-07-21 00:00:00
 3  Sulabh  Manesar 1975-09-11 00:00:00
 4  Matthew Palmer  1983-12-28 00:00:00
 5  Samuel  Maxwell 1984-05-22 00:00:00

Now my original table is having about 17 columns and this is just a simpler version of it. The tables 'A' and 'D' are generated by a query. Table 'A' will be populated and Table D will be like a temporary table that gets its values from the query, compares all the First Names of each table and if it encounters any change , it needs to update the log table By the first name and also mentions all the columns that are different.

For this I have created a temporary table, viz. '#TMP_COMPARE' which takes all the columns of table 'a'. and then compares those columns against that of table 'd' and it has the columns PLN, PDOB, Pmatch which have values 0 by default and are set to one in case all columns match for that row(Pmatch=1), Last name matches (PLN=1), Dob matches (Pdob=1).

Once this '#TMP_COMPARE' compares the two tables I will then update the log table with the columns that dont match for a first name.

Code:

USE Testing
GO

IF OBJECT_ID('TEMPDB..#TMP_COMPARE') is not null
BEGIN
DROP TABLE #TMP_COMPARE
END 

CREATE TABLE #TMP_COMPARE(
FN varchar(20),
LN varchar(20),
dob smalldatetime,
PLN int default 0,
Pdob int default 0,
 Pmatch int default 0)

BEGIN
INSERT INTO #TMP_COMPARE
SELECT a.fn, a.ln, a.dob, 
case when a.ln = d.Last_name AND a.dob = d.Birthdate
        THEN 1          
        END AS #TMP_COMPARE.PMATCH,--Error in this line ..COMPARE.PM.. 
 CASE WHEN a.dob <> d.Birthdate
        THEN 0  
         WHEN a.dob = d.Birthdate then 1 
        END AS #TMP_COMPARE.Pdob,           

 CASE WHEN a.ln <> d.Last_name 
        THEN 0
          WHEN a.ln = d.Last_name            
    then 1          
 END AS #TMP_COMPARE.PLN

FROM dbo.a2 as a  
 JoIN  d ON a.fn = d.First_Name     
END 

SELECT * FROM #TMP_COMPARE

Error I am getting is :-

Code:

Msg 102, Level 15, State 1, Line 24
Incorrect syntax near '.'

What is wrong in my query, and should I do this any other way please advice. Now this is something very basic that is wrong in my query but I am a newbie and any help is very much appreciated.

Thanks in advance, DCS

dcs · #4 (**permalink**) 11-17-11, 12:02

Quote:

Originally Posted by Wim

I don't have MSSQL at hand, but this should work:

Code:

SELECT a.fn, a.ln, a.dbo, 
	CASE WHEN a.ln = d.Last_name AND a.dob = d.Birthdate
			THEN 'match'
		WHEN a.ln = d.Last_name AND a.dob <> d.Birthdate
			THEN 'DOB not match'
		WHEN a.ln <> d.Last_name AND a.dob = d.Birthdate
			THEN 'LN not match'
		ELSE -- a.ln <> d.Last_name AND a.dob <> d.Birthdate
			'LN and DOB not match'
	END AS log
FROM a  
	INNER JoIN dbo.d ON 
		a.fn = d.First_Name

Hi Wim, Many Thanks for your response that works splendidly, can you please advice me how to use a case in a table which has 17 columns to compare. Please read my second query. Sorry to waste ur time.

Internet Services

Web Development

Digital Marketing

Consumer Tech