﻿ SQL: List the referees with their countries for each match

# SQL Exercise: List the referees with their countries for each match

## SQL soccer Database: Joins Exercise-48 with Solution

48. From the following table, write a SQL query to find the name of referees for each match. Sort the result-set on match number. Return match number, country name, referee name.

Sample table: match_mast

Sample table: referee_mast

Sample table: soccer_country

Sample Solution:

SQL Code:

``````SELECT a.match_no,
b.country_name,
c.referee_name
FROM match_mast a
JOIN referee_mast c ON a.referee_id=c.referee_id
JOIN soccer_country b ON c.country_id=b.country_id
ORDER BY a.match_no;
```
```

Sample Output:

``` match_no |  country_name  |      referee_name
----------+----------------+-------------------------
1 | Hungary        | Viktor Kassai
2 | Spain          | Carlos Velasco Carballo
3 | Norway         | Svein Oddvar Moen
4 | Italy          | Nicola Rizzoli
5 | Sweden         | Jonas Eriksson
6 | Romania        | Ovidiu Hategan
7 | England        | Martin Atkinson
8 | Poland         | Szymon Marciniak
9 | Serbia         | Milorad Mazic
10 | England        | Mark Clattenburg
11 | France         | Clement Turpin
12 | Turkey         | Cuneyt Cakir
13 | Slovenia       | Damir Skomina
14 | Russia         | Sergei Karasev
15 | Scotland       | William Collum
16 | Germany        | Felix Brych
17 | Czech Republic | Pavel Kralovec
18 | Netherlands    | Bjorn Kuipers
19 | Hungary        | Viktor Kassai
20 | England        | Mark Clattenburg
21 | Serbia         | Milorad Mazic
22 | Turkey         | Cuneyt Cakir
23 | Russia         | Sergei Karasev
24 | Italy          | Nicola Rizzoli
25 | Czech Republic | Pavel Kralovec
26 | Slovenia       | Damir Skomina
27 | Sweden         | Jonas Eriksson
28 | Spain          | Carlos Velasco Carballo
29 | Norway         | Svein Oddvar Moen
30 | France         | Clement Turpin
31 | Scotland       | William Collum
32 | Netherlands    | Bjorn Kuipers
33 | Poland         | Szymon Marciniak
34 | England        | Martin Atkinson
35 | Romania        | Ovidiu Hategan
36 | Germany        | Felix Brych
37 | England        | Mark Clattenburg
38 | England        | Martin Atkinson
39 | Spain          | Carlos Velasco Carballo
40 | Italy          | Nicola Rizzoli
41 | Poland         | Szymon Marciniak
42 | Serbia         | Milorad Mazic
43 | Turkey         | Cuneyt Cakir
44 | Slovenia       | Damir Skomina
45 | Germany        | Felix Brych
46 | Slovenia       | Damir Skomina
47 | Hungary        | Viktor Kassai
48 | Netherlands    | Bjorn Kuipers
49 | Sweden         | Jonas Eriksson
50 | Italy          | Nicola Rizzoli
51 | England        | Mark Clattenburg
(51 rows)
```

Code Explanation:

The said query in SQL that selects the match number, country name, and referee name for each match in the match_mast table.
The JOIN clause in this query then joins the match_mast and referee_mast tables based on the referee_id column and joins the results with soccer_country tables based on the country_id column.
The query then orders the results by match number in ascending order using the ORDER BY clause.

Relational Algebra Expression:

Relational Algebra Tree:

## Practice Online

Sample Database: soccer

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Countries where assistant referees assist most matches.
Next SQL Exercise: The number of matches each referee has managed.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿

## SQL: Tips of the Day

What is the best way to paginate results in SQL Server?

```SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
FROM      Orders
WHERE     OrderDate >= '1980-01-01'
) AS RowConstrainedResult
WHERE   RowNum >= 1
AND RowNum < 20
ORDER BY RowNum
```

Database: SQL Server

Ref: https://bit.ly/3MGrNlk

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook