CODEWITHSUNDEEP
sqlpostgresql
Prakash_se7en
Prakash_se7en

Reputation: 88

forming subquery in postgres sql

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?

Upvotes: 0

Views: 35

Answers (2)

jpw
jpw

Reputation: 44931

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

Upvotes: 2

PeterRing
PeterRing

Reputation: 1797

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)

Upvotes: 1

Related Questions