SQL Finding one of two elements in one table

Question

I have table matches with elements id that is PK, p1_name, p2_name, p1_score, p2_score.
If I know id and name of one of p's, how do I found score of this p?

Answer 1

Assuming your DBMS tag is mysql( formerly mysql, sqlite existed ), you may use the following syntax with bind variables made up of case..when statement :

SET @p_id = <myInt>; 
SET @p_name = '<myStringValue>';
select  ( case when `p1_name` = @p_name 
               then `p1_score` 
               else `p2_score`
               end ) as score
  from `matches`
 where `id` = @p_id
   and @p_name in (`p1_name`, `p2_name`);

or

substiute a quoted literal instead for @p_name and an integer for @p_id as in the SQL Fiddle Demo