Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2011
    Posts
    5

    Unanswered: Need to compare each rows in two tables

    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

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Sep 2011
    Posts
    5

    Comparing two tables for each column of each row and result as log variable

    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

  4. #4
    Join Date
    Sep 2011
    Posts
    5

    Thanks WIM

    Quote Originally Posted by Wim View Post
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •