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


1 Answer 1

SELECT x.id1, x.id2 
FROM   tbl x
    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.

  • 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, 2012 at 2:08
  • Many thanks @Erwin this works perfectly, you save my day
    – ReynierPM
    May 9, 2012 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, 2012 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. May 11, 2012 at 22:22
  • @erwin-brandstetter thaks that was the problem I didn't create the INDEX right now it works
    – ReynierPM
    May 15, 2012 at 13:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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