CODEWITHSUNDEEP
sqlite
vrleboss
vrleboss

Reputation: 471

Conditional selection of field values

I am working on a database created for sport games.

I have one table called game:

-----------------------------
winner  | team_id1 | team_id2
-----------------------------

I have a table teams

----------
id | title
----------

if winner = 1, team_id1 is the id of the winner team

if winner = 2, team_id2 is the id of the winner team

At the end I want for each games to get the title of the winner team followed by the title of the loser team.

It tried something like:

select tea1.title, tea2.title
from games gam
join teams tea1 on case
when gam.winner = 1 and gam.team1_id = tea1.id then 1
when gam.winner = 2 and gam.team2_id = tea1.id then 1
else 0
end = 1
join teams tea2 on case
when gam.winner = 2 and gam.team1_id = tea2.id then 1
when gam.winner = 1 and gam.team2_id = tea2.id then 1
else 0
end = 1

But the request hangs, I don't understand why.

Upvotes: 1

Views: 23

Answers (1)

Jaugar Chang
Jaugar Chang

Reputation: 3196

Try to get winner or loser title in select clause instead of join conditions.

select
    gam.winner,
    case when gam.winner=1 then t1.title else t2.title end as winner_title,
    case when gam.winner=2 then t1.title else t2.title end as loser_title
from
    games gam
left join
    teams t1
on
    gam.team1_id = t1.id
left join
    teams t2
on
    gam.team2_id = t2.id

Upvotes: 2

Related Questions