none
SQL Server

    Question

  • I have two tables in first table and second table having two common colums but i want to retrive the first table colums those are not matching to the second table values.

    pls help me

    Thanks in Advance

    Saturday, June 29, 2013 12:23 PM

Answers

  • I'm not exactly sure about your expected results.  You will help us help you better if you post DDL (CREATE TABLE statements), sample data and expected results.  Below is an example of how you might accomplish this using a LEFT JOIN as I understand your question.  You can also use NOT EXISTS.

    CREATE TABLE dbo.Table1(
    	Column1 int NOT NULL
    	,Column2 int NOT NULL
    	);
    
    CREATE TABLE dbo.Table2(
    	Column1 int NOT NULL
    	,Column2 int NOT NULL
    	);
    
    INSERT INTO dbo.Table1 VALUES
    	(1,1)
    	,(1,2)
    	,(2,1);
    
    INSERT INTO dbo.Table2 VALUES
    	(1,1)
    	,(1,2)
    	,(1,3)
    	,(2,2);
    
    SELECT t1.Column1, t1.Column2
    FROM dbo.Table1 AS t1
    LEFT JOIN dbo.Table2 AS t2 ON
    	t2.Column1 = t1.Column1
    	AND t2.Column2 = t1.Column2
    WHERE
    	t2.Column1 IS NULL; --not matched


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, June 29, 2013 12:56 PM

All replies

  • I'm not exactly sure about your expected results.  You will help us help you better if you post DDL (CREATE TABLE statements), sample data and expected results.  Below is an example of how you might accomplish this using a LEFT JOIN as I understand your question.  You can also use NOT EXISTS.

    CREATE TABLE dbo.Table1(
    	Column1 int NOT NULL
    	,Column2 int NOT NULL
    	);
    
    CREATE TABLE dbo.Table2(
    	Column1 int NOT NULL
    	,Column2 int NOT NULL
    	);
    
    INSERT INTO dbo.Table1 VALUES
    	(1,1)
    	,(1,2)
    	,(2,1);
    
    INSERT INTO dbo.Table2 VALUES
    	(1,1)
    	,(1,2)
    	,(1,3)
    	,(2,2);
    
    SELECT t1.Column1, t1.Column2
    FROM dbo.Table1 AS t1
    LEFT JOIN dbo.Table2 AS t2 ON
    	t2.Column1 = t1.Column1
    	AND t2.Column2 = t1.Column2
    WHERE
    	t2.Column1 IS NULL; --not matched


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, June 29, 2013 12:56 PM
  • Thanks for reply..
    Tuesday, July 23, 2013 12:16 PM