Need a DB puzzle solved

General
1

I have a table with two fields that hold indentifiers for a “person”

Given the ID of one person I need to find the other
Finding the RECORD is easy

SELECT * FROM myTABLE where (id1='xyz' OR id2='xyz')

but I need to return only the “other” id… and it could be id1 or id2 depending on which field my key is in which could be either

of course one way would be a union, but there is more the the query, and I don’t want to duplicate all that if it can be avoided

SELECT id1 as newID FROM myTable where id2='xyz'
UNION
SELECT id2 as newID FROM myTable where id1='xyz'
2

I think this will do it (haven’t tested it yet)

SELECT 
CASE id1
WHEN 'xyz' then id2
ELSE id1
END as newID
3

I’d post the full code but I’m on my phone.

Consider a WITH statement that creates a union and normalize the column names. From there, it’s a simple SELECT.

4

The idea was to AVOID the union (I posted that possible solution in my first post)

5

My solution is a bit different.

6

To wit, your code involved duplicating the WHERE, but my suggestion does not.

7

Now that I’m back, here is the code I was recommending:

with normalized as (
  (select id1 as this_id, id2 as that_id from myTABLE)
  UNION
  (select id2 as this_id, id1 as that_id from myTABLE)
)
select
  that_id
from
  normalized
where
  this_id = 'xyz'
8

SELECT CASE id1 WHEN 'xyz' then id2 ELSE id1 END as newID
and that is easier than this?

Not to mention the long complex SELECT code (which i left out to simplfy) would need to be duplicated

Sorry, but I have to disagree.

9

I wasn’t starting an argument, just illustrating my earlier suggestion.

10

nor was I… I just disagree that your solution is cleaner than mine. the CASE is just a replacement for the one field in a single select statement, where yours would require the select logic be duplicated, and/or another level of sub query.

11

SELECT CASE id1 WHEN 'xyz' THEN id2 ELSE id1 END AS newID WHERE id1='xyz' OR id2='xyz' -- If id1 and id2 are indexed this is very fast, without this "where" all the DB will be scanned

12

Yes, that is what the actual code said… I just posted the one field related portion

and as I mentioned there is a whole lot more to that WHERE statement in the actual code.