return 1-to-1 id's

[jodugg]

I have a table with two columns: id1, id2. There are 4 scenerios regarding these fields: 1) A id1 can map to multiple id2's 2) a id2 can map to multiple id1's 3) A id1 can map to one id2 4) a id2 can map to one id2. I want to create a table that contains all id1's and id2's that only match in a 1-to-1 relationship - meaning, the id1 is only related to one id2. What would that select statement look like? Thanks in advance, experts!

 

[jodugg]

The database is Oracle 10g...

 

[SantaMufasa]

Jodugg,

There are many, many ways to solve your need. Here is one of them:

select * from jodugg;

       ID1        ID2
---------- ----------
         1          9
         1          8
         2          3
         3          2
         4          5
         4          6
         7         10
        10          7

8 rows selected.

select id1, id2
  from (select id1, id2 from jodugg
         union all
        select id2, id1 from jodugg)
 group by id1, id2
having count(*) = 2
/

       ID1        ID2
---------- ----------
         2          3
         3          2
         7         10
        10          7

4 rows selected.

Let us know if this is useful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[jodugg]

Curious - why would you select having count(*) = 2? Since we need only the 1-to-1 records, shouldn't this be having count(*) = 1?

 

[SantaMufasa]

Curious - why would you select having count(*) = 2? Since we need only the 1-to-1 records, shouldn't this be having count(*) = 1?

Good question, JoDugg. I used "HAVING COUNT(*) = 2" as a logic "trick"...Notice my inner "SELECT...UNION":

select id1, id2 from jodugg
 union all
select id2, id1 from jodugg

...I selected "ID1, ID2" in the first SELECT, then I swapped the order of the two expressions to read "ID2, ID1". By doing so, and filtering with "HAVING COUNT(*) = 2", the only results will be cases with exactly a 1-to-1 relationship.


If that explanation did not clarify satisfactorily, please advise.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ChrisHunt]

I think you need to define the problem a little more precisely:

I want to create a table that contains all id1's and id2's that only match in a 1-to-1 relationship - meaning, the id1 is only related to one id2

What about id2s? Is it OK for the id2 to be related to other id1s?

I'm going to assume that it isn't, and that you want is id1, id2 pairs where neither id appears in any other row of the table. Off the top of my head I can think of a few approaches:

SELECT id1,id2
FROM   my_table a
WHERE NOT EXISTS
     (SELECT 99
      FROM   my_table b1
      WHERE  b1.id1 = a.id1
      AND    b1.rowid != a.rowid)
AND NOT EXISTS
     (SELECT 99
      FROM   my_table b2
      WHERE  b2.id2 = a.id2
      AND    b2.rowid != a.rowid)

or you could combine the two subqueries into one with a UNION:

SELECT id1,id2
FROM   my_table a
WHERE NOT EXISTS
     (SELECT 99
      FROM   my_table b1
      WHERE  b1.id1 = a.id1
      AND    b1.rowid != a.rowid
      UNION
      SELECT 99
      FROM   my_table b2
      WHERE  b2.id2 = a.id2
      AND    b2.rowid != a.rowid)

or you could do something a bit more like Santa's approach with HAVINGs and COUNTs:

SELECT id1,id2
FROM   my_table a
WHERE a.id1 IN (SELECT b1.id1
                FROM my_table b1
                GROUP BY b1.id1
                HAVING count(*) = 1)
AND   a.id2 IN (SELECT b2.id2
                FROM my_table b2
                GROUP BY b2.id2
                HAVING count(*) = 1)

I haven't tested any of these, so you might need to do a bit of trial and error to find the most efficient solution for your data.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd