CODEWITHSUNDEEP
sqlduplicatesunique
user3813903
user3813903

Reputation: 21

Searching for non unique values between two columns

I have a single table with three columns, 'id', 'number' and 'transaction.' Each id should only be tied to one number however may exist many times in the table under different values of transaction. I've been unable to develop a query that will return cases of a single id sharing multiple numbers (and show the id and number in the report). I don't wish to delete these values via the query, I just need to see the values. See example below:

Here's a screenshot example: http://i591.photobucket.com/albums/ss355/riggins_83/table2_zps5509f3cf.jpg I appreciate the assistance, I've tried all the code posted here and it hasn't given me the output I'm looking for. As seen in the screenshot it's possible for the same ID number and Number to appear in the table multiple times with a different transaction number, what shouldn't occur is what's on rows 1 and 2 (two different numbers with same ID number). The ID number is a value that should always be tied to the same Number which the transaction is only linked to that line. I'm trying to generate output of each number that's sharing an ID number (and the shared ID Number if possible).

Test    IDNumber    Number  Transaction
1        31         1551    5
2        31         1553    7
3        32         1701    8
4        33         1701    9
5        33         1701   10
6        33         1701   11
7        39         1885   12

The result of output I would need:

IDNumber    Number
31            1551
31            1553

This output is showing me the Number (and ID number) in cases where an ID number is being shared between two (or possibly more) numbers. I know there are cases in the table where an ID number is being shared among many numbers.

Any assistance is greatly appreciated!

Upvotes: 1

Views: 58

Answers (2)

Barmar
Barmar

Reputation: 781300

SELECT IDNumber, Number
FROM YourTable
WHERE IDNumber IN (
    SELECT IDNumber
    FROM YourTable
    GROUP BY IDNumber
    HAVING COUNT(DISTINCT number) > 1
)

The subquery returns all the IDNumbers with more than 1 Number. Then the main query returns all the numbers for each of those IDNumbers.

DEMO

Upvotes: 1

wildplasser
wildplasser

Reputation: 44250

SELECT *
FROM thetable t0
WHERE EXISTS (
    SELECT *
    FROM thetable t1
    WHERE t1.id = t0.id
           -- Not clear from the question if the OP wants the records
           -- to differ by number
    -- AND t1.number <> t1.number
       -- ... or by "transaction"
    AND t1."transaction" <> t0."transaction"
       -- ... or by either ???
    -- AND (t1.number <> t1.number OR t1."transaction" <> t0."transaction")
    );

Upvotes: 2

Related Questions