Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have this table structure:

id1   id2
 1     2
 1     3
 2     1
 2     4

id1 and id2 are not unique as you can see in the values. I need to retrieve those values that exists in the two columns meaning id1=1,2 id2=2,1. Any help?

Cheers and thanks in advance

share|improve this question
    
Is the combination of id1 and id2 unique? –  Erwin Brandstetter May 9 '12 at 0:37

1 Answer 1

up vote 3 down vote accepted
SELECT x.id1, x.id2 
FROM   tbl x
WHERE  EXISTS (
    SELECT *
    FROM   tbl y
    WHERE  (y.id1, y.id2) = (x.id2, x.id1)
    );

Assuming that the combination of (id1, id2) is unique, so no DISTINCT. This would still allow (1,2) and (2,1) to coexist (and both will be returned), but no two rows with (1,2).

Also assuming id1 != id2 for every row (deriving from your test data and question). A unique entry (1,1) without doppelganger would be returned by this query. If my first assumption is true, you can exclude id1 = id2 right away. Else, you'd need something like row_number() to identify duplicates ...

A multicolumn index would greatly speed up such a query:

CREATE UNIQUE INDEX tbl_id1_id2_idx ON tbl (id1, id2);

Use it without UNIQUE keyword, should (id1, id2) not be unique after all.


Or, you can use those other techniques discussed under your prequel question, accordingly modified.

share|improve this answer
    
Brandstetter, yes they are unique because only one 1,2 is allowed altough my bad because I completely forgot to make this two fields unique when build the DB. I'll test the queries tomorrow at office because I can't from home anyway this is a table for relation between users where id1 (user who start the relation) invites id2(user who receives the invitation). Then if id1=1 then id2 can't be 1 because a user can't invite himself. So id1=1, id2=2 can exists. So if id2 accept the invitation a new row will be created with values id1=2, id2=1 so I can know that user id=2 accepts. –  ReynierPM May 9 '12 at 2:08
    
Many thanks @Erwin this works perfectly, you save my day –  ReynierPM May 9 '12 at 13:22
    
Hi @erwin-brandstetter I try with UNIQUE fields as you suggested before but now I'm trying to insert another couple of id1,id2 and I can't. See the results below: Duplicate entry '2' for key 'id1' INSERT INTO default_relations_users (id1, id2) VALUES ('2', '4') –  ReynierPM May 11 '12 at 18:52
    
@ReynierPM: The error message suggests that you created a unique constraint on just id1, but I suggested a unique constraint on (id1, id2). Follow the link in my answer for details about multicolumn indexes. –  Erwin Brandstetter May 11 '12 at 22:22
    
@erwin-brandstetter thaks that was the problem I didn't create the INDEX right now it works –  ReynierPM May 15 '12 at 13:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.