> CREATE TABLE teams (id,name);
> CREATE TABLE games (id, date, team1_id, team2_id, result);
>
> team1_id and team2_id refer to the id in the teams table.
>
> 1. What query would be best suited to get an output so that the
> output would contain the teams names (not only the id) and the
> dates and results?
select team1.name, team2.name, games.date, games.result
from games
inner join teams team1 on games.team1_id = team1.id
inner join teams team2 on games.team2_id = team2.id;
> 2. What query would be best if I want to search for games that
> two specific teams had played. For example, I want all the games
> that the teams with the names 'bla' and 'blub' played against
> each other. Is there any way to do that?
select team1.name, team2.name, games.date, games.result
from games
inner join teams team1 on games.team1_id = team1.id
and team1.name in ('bla', 'blub')
inner join teams team2 on games.team2_id = team2.id
and team2.name in ('bla', 'blub');
I didn't check spelling or stuff, but the syntax should be mostly correct.
