April 9, 2013 at 3:53 am
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
🙂
April 9, 2013 at 3:58 am
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.
April 9, 2013 at 4:07 am
I want to get the id from DATA table which has exact data in NEW_DATA table
🙂
April 9, 2013 at 4:28 am
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/61537April 9, 2013 at 4:32 am
Such a simple logic. Thanks.
🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply