I am doing some sql query exercises based on a relational schema.
I need to do the following two queries:
- Get which 2 teams have faced each other more times (with the ids of the teams is enough)
- Obtain that 2 teams have faced each other 11 times (with the ids of the teams it is enough)
The corresponding section of the schematic is:
I have tried with the following code:
SELECT
COUNT(Match_Id) AS Contador_Partidos,
Team_1 AS Equipo_Local,
Team_2 AS Equipo_Visitante
FROM
MATCH
GROUP BY
Equipo_Local,
Equipo_Visitante
ORDER BY
Contador_Partidos DESC
LIMIT 30
But I don't get a conclusive result. Thanks for your help. All the best.
To obtain the teams that have met the most times, you can do the following:
Using the inner join clause you relate the match and team tables to get the team1 data and then use the inner join again to relate the same tables again, but this time to get the team2 data . Then you group by team names and sort descending to get only the first record, selecting team1 name , team2 name and counting the total times they have met.
To obtain that two teams have met 11 times, the query is similar to the previous one:
As you can see here, the only thing that changes with respect to the previous query is that you use the having clause to obtain the teams whose total matches are 11.