Identify matching records in single column query

Posted on 2005-04-30
Last Modified: 2010-03-19
Hi All,

I have the following table:

ID | Name | Level

In this table each Name has a Level (1-6).  I need a query that will return all the rows where there is a level 4 and 5 record with an identical name.


ID | Name | Level
1     dog       5
2     cat        5
3     sheep    4
4     horse    4
5     cow      5
6     cat       4

In the above example the query should return record 6 "cat" and record 2 "cat".

Hope this makes sense.

Question by:tim_chamberlain
    LVL 17

    Accepted Solution

    SELECT a.ID, a.Name, a.Level FROM test12 a
    JOIN test12 b ON a.Name=b.Name
    WHERE (a.Level=4 AND b.Level=5) OR (a.Level=5 AND b.Level=4)

    Author Comment

    Perfect. Thanks very much.

    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

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    755 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

    18 Experts available now in Live!

    Get 1:1 Help Now