forming subquery in postgres sql

Question

Below is the fixtures table which shows matches played against (here numbers refer to team ids).

   team_1 | team_2
 ----+--------+--------
        2 |      3
        4 |      5
        1 |      2
        4 |      3
        1 |      4
        2 |      5

i need to fetch matches played against team 2 which should be 1,5,3 is there any way query can help combining team_1 and team_2 and return 1,5,3. i tried

select * from fixtures where team_1  = 2 or team_2 = 2;

with the above i can able to get matches played against 2 shown below(I) but i need 1,5,3 in single column as shown in (II)

(I) | team_1 | team_2
   +--------+--------
   |      2 |      3
   |      1 |      2
   |      2 |      5

(II) teams
    -----
     3
     1
     5

is it possible using sql query?

jpw · Accepted Answer · 2015-04-26 18:26:02Z

2

One way of many would be to use a case statement to return the column that isn't team = 2:

select case when team_1 = 2 then team_2 else team_1 end as opposing_team
from fixtures where team_1  = 2 or team_2 = 2;

This would return:

opposing_team
3
1
5

answered Apr 26, 2015 at 18:26

jpw

45k6 gold badges71 silver badges90 bronze badges

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

Comments

PeterRing · Accepted Answer · 2015-04-26 18:25:55Z

1

Try this. A case statment can do the trick

select Case when team_1 = 2 then team_2 ELSE team_1 END
    from fixtures where 2 in  (team_2,team_1)

answered Apr 26, 2015 at 18:25

PeterRing

1,81713 silver badges21 bronze badges

Collectives™ on Stack Overflow

forming subquery in postgres sql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related