Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Compare two data sets-- Please help Expand / Collapse
Author
Message
Posted Tuesday, April 9, 2013 3:53 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 12, 2014 7:10 AM
Points: 167, Visits: 422
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


Post #1440239
Posted Tuesday, April 9, 2013 3:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:23 AM
Points: 5,074, Visits: 11,852
What results do you want to see, based on your sample data?




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1440242
Posted Tuesday, April 9, 2013 4:07 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 12, 2014 7:10 AM
Points: 167, Visits: 422
I want to get the id from DATA table which has exact data in NEW_DATA table

Post #1440245
Posted Tuesday, April 9, 2013 4:28 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 1:49 AM
Points: 1,694, Visits: 19,551
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);



____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1440250
Posted Tuesday, April 9, 2013 4:32 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 12, 2014 7:10 AM
Points: 167, Visits: 422
Such a simple logic. Thanks.

Post #1440253
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse