How to find whether two attributes have 1:1 mapping in a given dataset using sql?

Question

I have the following data given, what I am trying to do is to find if the cardinality between name and id is 1:1

Sample Data:

Id   Name
1    ABC
2    PQR
1    XYZ
4    ABC
90   PQR
100  LMN
30   DEF

Example: I want to detect the following problems: 1) Id "1" maps to more than one names ("ABC", LMN") 2) Name "PQR" corresponds to more than one Ids ("2", "90") 3) Name "ABC" corresponds to more than one Ids ("1", "4")

SELECT COUNT(Id) AS IdCount,
       Count(Name) AS NameCount
FROM InputData;

However, this will not tell me the answer I want, since there could be equal number of unique ids and names and still have more than one mapping among them. I don't care about duplicates for eg. if "1" corresponds to "ABC" multiple times, that should be okay.

What variety of SQL are you using (MySQL, SQL-Server, Oracle etc.)? — Nick, Feb 20 '19 at 01:02
Please make clear exactly what you want returned. You only say vague things like "detect the following problems". PS What does this have to do with relational algebra? (Other than what is implied by tagging with SQL.) — philipxy, Feb 20 '19 at 07:11
"I don't care about duplicates for eg. if "1" corresponds to "ABC" multiple times, that should be okay." You only have attributes `Id`, `Name`. Then if you have duplicate pairings that means duplicate tuples in the relation. Which means it's a bag, not a set. Which means it's not a relation. Which means you can't be talking about relational algebra. Neither could it be a SQL base table. — AntC, Feb 20 '19 at 09:28
@AntC SQL base tables can have (SQL-)duplicate rows. Which I expect you know. — philipxy, Feb 20 '19 at 22:09

score 1 · Accepted Answer · answered Feb 20 '19 at 01:06

I think what you want is the same as detecting duplicates in either column, in which case I'll adapt an answer from here.

Ids mapped to more than one Name:

SELECT
    Id, COUNT(*)
FROM
    InputData
GROUP BY
    Id
HAVING 
    COUNT(*) > 1

Names mapped to more than one Id:

SELECT
    Name, COUNT(*)
FROM
    InputData
GROUP BY
    Name
HAVING 
    COUNT(*) > 1

score 1 · Answer 2 · answered Feb 20 '19 at 01:06

Here's one way of doing it, using a UNION of two SELECTs, one which finds all the problem Id values and the other which finds the problem Name values:

SELECT 'Id' AS Type, CAST(Id AS CHAR) AS Problem
FROM InputData
GROUP BY Id
HAVING COUNT(DISTINCT Name) > 1
UNION
SELECT 'Name', Name
FROM InputData
GROUP BY Name
HAVING COUNT(DISTINCT Id) > 1

Output

Type    Problem
Id      1
Name    ABC
Name    PQR

Demo on dbfiddle

score 0 · Answer 3 · answered Feb 20 '19 at 01:20

Just simply do:

select (case when count(*) = count(distinct id) and
                  count(*) = count(distinct name)
             then '1-1'
             else 'Ooops'
        end)
from inputdata id;

In other words, the relationship is 1-1 if all the ids are distinct and all the names are distinct.

If your data might contain "complete" duplicates, such as:

1  ABC
1  ABC

Then change the from clause to:

from (select distinct id, name from inputdata) id

How to find whether two attributes have 1:1 mapping in a given dataset using sql?

3 Answers3