Compare two data sets-- Please help

• Ram:)

  SSCrazy

  Points: 2246

  More actions

  April 9, 2013 at 3:53 am

  #291934

  Hi,

  I have a scenario while comparing the data between two data sets.

  CREATE TABLE DATA

  (ID BIGINT,

  CODE VARCHAR(100),

  CNAME VARCHAR(100))

  TRUNCATE TABLE DATA

  INSERT INTO DATA

  SELECT 1,'INSURANCE','MONTHLY'

  UNION

  SELECT 1,'E-RD','MONTHLY'

  UNION

  SELECT 1,'RD','MONTHLY'

  UNION

  SELECT 2,'INSURANCE','BI-MONTHLY'

  UNION

  SELECT 2,'E-RD','QUARTERLY'

  UNION

  SELECT 2,'RD','MONTHLY'

  UNION

  SELECT 3,'INSURANCE','YEARLY'

  UNION

  SELECT 3,'E-RD','MONTHLY'

  UNION

  SELECT 3,'RD','MONTHLY'

  UNION

  SELECT 3,'FD','MONTHLY'

  UNION

  SELECT 4,'INSURANCE','YEARLY'

  UNION

  SELECT 4,'E-RD','MONTHLY'

  UNION

  SELECT 4,'RD','MONTHLY'

  CREATE TABLE NEW_DATA

  (CODE VARCHAR(100),

  CNAME VARCHAR(100))

  INSERT INTO NEW_DATA

  SELECT 'INSURANCE','YEARLY'

  UNION

  SELECT 'E-RD','MONTHLY'

  UNION

  SELECT 'RD','MONTHLY'

  Here I need to compare data from table NEW_DATA with table DATA and need to pick id from Table DATA which have the same set of records.. In the above scenario id 3 and 4 can be compared with NEW_DATA but id 4 only has same set of data..

  Please help me with query

  🙂

• Phil Parkin

  SSC Guru

  Points: 246695

  More actions

  April 9, 2013 at 3:58 am

  #1604739

  What results do you want to see, based on your sample data?

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• Ram:)

  SSCrazy

  Points: 2246

  More actions

  April 9, 2013 at 4:07 am

  #1604742

  I want to get the id from DATA table which has exact data in NEW_DATA table

  🙂

• Mark Cowne

  One Orange Chip

  Points: 26944

  More actions

  April 9, 2013 at 4:28 am

  #1604746

  Maybe this?

  SELECT d.ID

  FROM DATA d

  LEFT OUTER JOIN NEW_DATA n ON n.CODE = d.CODE AND n.CNAME=d.CNAME

  GROUP BY ID

  HAVING COUNT(*) = COUNT(n.CNAME);

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Ram:)

  SSCrazy

  Points: 2246

  More actions

  April 9, 2013 at 4:32 am

  #1604749

  Such a simple logic. Thanks.

  🙂