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

Posted on 2008-11-19
Last Modified: 2013-11-16
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

Dataset 2:

ID  Value
1    Comparing
2    7777
3    123456
4    XYZAB

Dataset 3:

ID  Value
1    CompareTest
2    7771
3    54321

......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!

Question by:OddiC
    LVL 40

    Accepted Solution

    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 DataSet1
    SELECT ID,Value
      FROM DataSet2
    SELECT ID,Value
    ------ Include all your Datasets here with UNION ALL
    GROUP BY ID,Value
    HAVING COunt(*) > 1

    Open in new window

    LVL 40

    Expert Comment

    I assumed that combination of ID and Value is unique in one Dataset.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now