Query same thing in multiple columns?

Question

I have a table where there are some scores for pairs.

Like 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
...

So what I want is to get all the scores for Sinan and his partner. What I am trying to get is something llike:

partner - score
ash       53
ash       54
suzie     42

I'm trying to do it with te query below. Is there a better way to query than

select * from table WHERE P1 = 'sinan' OR P2 = 'sinan'

Is this efficient? Maybe there is a better way to store the data in the first place. Any suggestions?

How large is the data table? That is probably the simplest method, even though it's technically not normalized. Unless you need really high performance, I wouldn't bother optimizing it any more. — mellamokb
– mellamokb, Commented Aug 4, 2011 at 13:42

Joe Stefanelli · Accepted Answer · 2011-08-04 13:43:55Z

3

The real trick is alternating the partner between P1 and P2. The simplest approach might be:

SELECT P2 AS partner, Score
    FROM table
    WHERE P1 = 'sinan'
UNION ALL
SELECT P1 AS partner, Score
    FROM table
    WHERE P2 = 'sinan'

answered Aug 4, 2011 at 13:43

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Derek Over a year ago

Do you think a case statement with the OR clause would perform better than a union? select case when p1='sinan' then p2 else p1 end as partner from table where p1='sinan' or p2='sinan'

Joe Stefanelli Over a year ago

@Derek: That's a good suggestion too. I was actually going to add it as an alternative option when your comment came in. I'd suggest benchmarking both.

Narnian · Accepted Answer · 2011-08-04 15:52:47Z

0

Here's an example using the CASE statement

SELECT CASE WHEN P1 = 'Sinan' THEN P2 ELSE P1 END AS Partner, Score
FROM ScoreTable
WHERE P1 = 'Sinan' OR P2 = 'Sinan'

answered Aug 4, 2011 at 15:52

Narnian

3,9181 gold badge28 silver badges29 bronze badges

Collectives™ on Stack Overflow

Query same thing in multiple columns?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related