Pick relevant value in a set of columns

It might be an easy question for DBA, but not for me. I have a simplified Family table that looks like that: (part1 and part2 are ids)

famid	indiv1	indiv2
1	42	27
2	33	22
3	42	12
4	22	42
5	42	27

I can easily retrieve all families that match part1=X or part2=X But the requested output format is to produce only the famid AND (part1 value or part2 value) BUT without the column that matches the value X. For example, for a value of 42, the query should return: [[1,27], [3,12], [4,22], [5,27]] I would like to know if there is a ‘simple’ way to produce such a result with SQL query (or sequelize) only, but without a stored procedure. Thx

Answer

Assuming you only have two columns to check and want to return the one that doesn’t = X then you want to use a CASE statement to check if Indiv1 = 42. Then if it is return Indiv2 otherwise return Indiv1.

SELECT FamID
    ,CASE 
        WHEN Indiv1 = 42
            THEN Indiv2
        ELSE Indiv1
        END AS Indiv
FROM Family
WHERE Indiv1 = 42
    OR Indiv2 = 42

See my SQL Fiddle Demo