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: Thursday, October 9, 2014 11:30 PM
Points: 167, Visits: 427
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 @ 1:39 PM
Points: 5,307, Visits: 12,334
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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
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: Thursday, October 9, 2014 11:30 PM
Points: 167, Visits: 427
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: Today @ 2:11 PM
Points: 1,681, Visits: 19,596
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: Thursday, October 9, 2014 11:30 PM
Points: 167, Visits: 427
Such a simple logic. Thanks.

Post #1440253
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse