• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

How can I compare multiple datasets in SQL and select only the common rows?

Hi everyone,

I have a unique request. I must do a comparison on the db side of 2 or more result sets, and locate only the rows that have matches in other datasets being compared.

In summary, I will have for example, 3 sets as shown below:


Dataset 1:

ID  Value
1    CompareTest
2    7777
3    123
4    ABCDEF

Dataset 2:

ID  Value
1    Comparing
2    7777
3    123456
4    XYZAB

Dataset 3:

ID  Value
1    CompareTest
2    7771
3    54321
4    ABBCCD


......So from the above, I want to return ONLY "7777" and Compare Test as below:

ID    Value
1     CompareTest
2      7777


That's all I want (only the common rows amongst all the datasets that I am comparing). Surely this is possible, no? (also- I must be able to support dynamic numbers of datasets to be compared (sometimes it will be 2- sometimes it may be 20).

This is a tough problem (for me!); any help or pointers you provide are greatly, greatly appreciated.


Thanks to all in advance!
 


0
OddiC
Asked:
OddiC
  • 2
1 Solution
 
SharathData EngineerCommented:
Check this query. In the inner query, i took 3 DataSets. You have to include all your Datasets in the inner query.
SELECT ID,Value FROM 
(SELECT ID,Value
  FROM DataSet1
 UNION ALL
SELECT ID,Value
  FROM DataSet2
 UNION ALL
SELECT ID,Value
------ Include all your Datasets here with UNION ALL
)
GROUP BY ID,Value
HAVING COunt(*) > 1

Open in new window

0
 
SharathData EngineerCommented:
I assumed that combination of ID and Value is unique in one Dataset.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now