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
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 only takes a minute to sign up.Sign up to join this community
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
(2,1) to coexist (and both will be returned), but no two rows with
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.