SQL Exercise: Find the match in which Germany played against Poland

Last update on January 16 2026 12:32:30 (UTC/GMT +8 hours)

4. From the following tables, write a SQL query to find the match number in which Germany played against Poland. Group the result set on match number. Return match number.

Sample table: match_details

 match_no | play_stage | team_id | win_lose | decided_by | goal_score | penalty_score | ass_ref | player_gk
----------+------------+---------+----------+------------+------------+---------------+---------+-----------
        1 | G          |    1207 | W        | N          |          2 |               |   80016 |    160140
        1 | G          |    1216 | L        | N          |          1 |               |   80020 |    160348
        2 | G          |    1201 | L        | N          |          0 |               |   80003 |    160001
        2 | G          |    1221 | W        | N          |          1 |               |   80023 |    160463
        3 | G          |    1224 | W        | N          |          2 |               |   80031 |    160532
        3 | G          |    1218 | L        | N          |          1 |               |   80025 |    160392
        4 | G          |    1206 | D        | N          |          1 |               |   80008 |    160117
        4 | G          |    1217 | D        | N          |          1 |               |   80019 |    160369
        5 | G          |    1222 | L        | N          |          0 |               |   80011 |    160486
.......
       51 | F          |    1207 | L        | N          |          0 |               |   80007 |    160140

View the table

Sample table: soccer_country

 country_id | country_abbr |    country_name
------------+--------------+---------------------
       1201 | ALB          | Albania
       1202 | AUT          | Austria
       1203 | BEL          | Belgium
       1204 | CRO          | Croatia
       1205 | CZE          | Czech Republic
       1206 | ENG          | England
       1207 | FRA          | France
       1208 | GER          | Germany
       1209 | HUN          | Hungary
.......
       1229 | NOR          | Norway

View the table

Sample Solution:

SQL Code:

-- This SQL query retrieves match numbers from the 'match_details' table
-- where the teams are either Germany or Poland, and each match involves both teams.

SELECT match_no 
-- Selects the 'match_no' column.
FROM match_details 
-- 'match_details' is the name of the table being queried.
WHERE team_id = (
-- The WHERE clause filters rows where 'team_id' matches the country ID for Germany in the subquery.
    SELECT country_id 
    -- The subquery selects the 'country_id' column.
    FROM soccer_country 
    -- 'soccer_country' is the name of the table involved in the subquery.
    WHERE country_name = 'Germany'
) 
OR team_id = (
-- The OR condition allows for rows where 'team_id' matches the country ID for Poland in the subquery.
    SELECT country_id 
    -- The subquery selects the 'country_id' column.
    FROM soccer_country 
    -- 'soccer_country' is the name of the table involved in the subquery.
    WHERE country_name = 'Poland'
) 
GROUP BY match_no 
-- Groups the results by 'match_no'.
HAVING COUNT(DISTINCT team_id) = 2;
-- The HAVING clause filters groups where the count of distinct 'team_id' values is equal to 2.

Sample Output:

 match_no
----------
       18
(1 row)

Code Explanation:

The said query in SQL that selects the match number from the 'match_details' table where either the team_id is Germany's country_id or Poland's country_id and there are two distinct team_ids associated with that match.
The WHERE clause filters the results to only include rows where the "team_id" column is equal to either Germany's country_id or Poland's country_id.
The GROUP BY clause groups the results by the "match_no" column.
The HAVING clause filters the results to only include groups where there are two distinct values of the "team_id" column.
So, this query will return the match numbers for matches where Germany or Poland participated and there were exactly two teams in the match.

Alternative Solutions:

Using JOIN and WHERE:

SELECT md.match_no 
FROM match_details md
JOIN soccer_country sc ON md.team_id = sc.country_id
WHERE sc.country_name IN ('Germany', 'Poland')
GROUP BY md.match_no
HAVING COUNT(DISTINCT md.team_id) = 2;

Explanation:

This query joins match_details with soccer_country based on the team_id and country_id relation. It then filters for matches involving Germany or Poland, groups the results by match number, and ensures that both teams are present in the match.

Using EXISTS:

SELECT match_no
FROM match_details md1
WHERE team_id = (
    SELECT country_id 
    FROM soccer_country 
    WHERE country_name='Germany'
)
AND EXISTS (
    SELECT 1
    FROM match_details md2
    WHERE md2.match_no = md1.match_no
    AND md2.team_id = (
        SELECT country_id 
        FROM soccer_country 
        WHERE country_name='Poland'
    )
);

Explanation:

This query uses two correlated subqueries to find matches where Germany and Poland both participated.

Using INTERSECT:

SELECT match_no 
FROM match_details 
WHERE team_id = (
    SELECT country_id 
    FROM soccer_country 
    WHERE country_name='Germany'
)
INTERSECT
SELECT match_no 
FROM match_details 
WHERE team_id = (
    SELECT country_id 
    FROM soccer_country 
    WHERE country_name='Poland'
);

Explanation:

This query uses the INTERSECT operator to find matches that involve both Germany and Poland.

Go to:

• SQL soccer Database Exercises Home. ↩
• SQL Exercises Home ↩

PREV : Find which was the highest audience match.
NEXT : Find the match where Portugal played against Hungary.

Practice Online

Sample Database: soccer

Query Visualization:

Duration:

Rows:

Cost:

• Daily Coding Challenges & Projects
• Weekly Trends and Language Statistics