I've got a table where you will find some scores for pairs.
Such as this
P1 P2 Score Date John Mark 43 2011-01-01 Sinan Ash 53 2011-02-03 ... John Suzie 34 2011-10-10 Ash Sinan 54 2011-11-11 sinan suzie 42 2011-12-12 ...
What exactly I would like is to buy all of the scores for Sinan and the partner. Things I am looking to get is one thing llike:
partner - score ash 53 ash 54 suzie 42
I am trying to get it done with te query below. It is possible to better method to query than
select * from table WHERE P1 = 'sinan' OR P2 = 'sinan'
Is efficient? Maybe there's an easy method to keep the information to begin with. Any suggestions?
The actual trick is alternating the partner between P1 and P2. The easiest approach may be:
SELECT P2 AS partner, Score FROM table WHERE P1 = 'sinan' UNION ALL SELECT P1 AS partner, Score FROM table WHERE P2 = 'sinan'
Here's a good example while using Situation statement
SELECT CASE WHEN P1 = 'Sinan' THEN P2 ELSE P1 END AS Partner, Score FROM ScoreTable WHERE P1 = 'Sinan' OR P2 = 'Sinan'