Problem with learning MANY to MANY relation in SQL

On which of your views do you meet problems?

On the 3rd one: you combine the 2 preceding views. Why not simply enhancing the 1st one to get both won and lost matches?

I've solved the problem (I think) but, being a novice, probably I wrote useless code?

What do you think of this:

CREATE VIEW v_players_per_tournament AS
    SELECT t_players.id as id_player, t_players.name as player_name, t_tournaments_players.id_tournament
    FROM t_players
    JOIN t_tournaments_players
    ON t_players.id = t_tournaments_players.id_player;


CREATE VIEW v_matches_lost_per_player_per_tournament AS
    SELECT v_players_per_tournament.id_player, v_players_per_tournament.player_name, v_players_per_tournament.id_tournament, COUNT(t_matches.id_loser) AS total_matches_lost
    FROM v_players_per_tournament
    LEFT JOIN t_matches ON v_players_per_tournament.id_player = t_matches.id_loser and v_players_per_tournament.id_tournament = t_matches.id_tournament
    GROUP BY v_players_per_tournament.id_player, v_players_per_tournament.player_name, v_players_per_tournament.id_tournament
    ORDER BY total_matches_lost DESC;

CREATE VIEW v_matches_won_per_player_per_tournament AS
    SELECT v_players_per_tournament.id_player, v_players_per_tournament.player_name, v_players_per_tournament.id_tournament, COUNT(t_matches.id_winner) AS total_matches_won
    FROM v_players_per_tournament
    LEFT JOIN t_matches ON v_players_per_tournament.id_player = t_matches.id_winner AND v_players_per_tournament.id_tournament = t_matches.id_tournament
    GROUP BY v_players_per_tournament.id_player, v_players_per_tournament.player_name, v_players_per_tournament.id_tournament
    ORDER BY total_matches_won DESC;

CREATE VIEW v_total_stats AS
    SELECT v_matches_won_per_player_per_tournament.id_player as id, v_matches_won_per_player_per_tournament.player_name as name,
           v_matches_won_per_player_per_tournament.total_matches_won as wins,
           v_matches_lost_per_player_per_tournament.total_matches_lost as losses,
           v_matches_won_per_player_per_tournament.id_tournament
    FROM v_matches_won_per_player_per_tournament, v_matches_lost_per_player_per_tournament
    WHERE v_matches_won_per_player_per_tournament.id_player = v_matches_lost_per_player_per_tournament.id_player AND v_matches_won_per_player_per_tournament.id_tournament = v_matches_lost_per_player_per_tournament.id_tournament
    GROUP BY v_matches_won_per_player_per_tournament.id_player, v_matches_won_per_player_per_tournament.player_name,
           v_matches_won_per_player_per_tournament.total_matches_won,
           v_matches_lost_per_player_per_tournament.total_matches_lost,
           v_matches_won_per_player_per_tournament.id_tournament;

Select allOpen in new window

Ps

I can't think of a method to get in a single passage both all wins and all losses...

Is this possible?

(your first pastebin with data structure has been removed, we need it back)

I copy everything again here, sorry:

-- Creates tournament database and use it.
CREATE DATABASE tournament ENCODING 'utf8';
\c tournament;

-- Creates the table that will hold all players.
CREATE TABLE t_players (

    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    surname TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL

    );

-- Creates the table that will hold all the tournaments.
CREATE TABLE t_tournaments (

    id SERIAL PRIMARY KEY,
    name TEXT

    );

-- Creates the table that will hold all match results.
CREATE TABLE t_matches (

    id SERIAL PRIMARY KEY,
    id_tournament INTEGER NOT NULL,
    id_loser INTEGER NOT NULL,
    id_winner INTEGER NOT NULL,

    constraint fk_tournament_id
    foreign key(id_tournament) references t_tournaments(id),

    constraint fk_loser_id
    foreign key(id_loser) references t_players(id),

    constraint fk_winner_id
    foreign key(id_winner) references t_players(id)

    );

-- Creates the table that will hold all the player per tournament registrations.
CREATE TABLE t_tournaments_players (

    id_player INTEGER NOT NULL,
    id_tournament INTEGER NOT NULL,

    constraint fk_player_id
    foreign key(id_player) references t_players(id),

    constraint fk_tournament_id
    foreign key(id_tournament) references t_tournaments(id),

    primary key (id_player, id_tournament)

  );

-- Creates the view that hold the players and the tournaments they are registered in.
CREATE VIEW v_players_per_tournament AS
    SELECT t_players.id as id_player, t_players.name as player_name, t_tournaments_players.id_tournament
    FROM t_players
    JOIN t_tournaments_players
    ON t_players.id = t_tournaments_players.id_player;

-- -- Creates the view that hold total matches lost per player per tournament.
CREATE VIEW v_matches_lost_per_player_per_tournament AS
    SELECT v_players_per_tournament.id_player, v_players_per_tournament.player_name, v_players_per_tournament.id_tournament, COUNT(t_matches.id_loser) AS total_matches_lost
    FROM v_players_per_tournament
    LEFT JOIN t_matches ON v_players_per_tournament.id_player = t_matches.id_loser and v_players_per_tournament.id_tournament = t_matches.id_tournament
    GROUP BY v_players_per_tournament.id_player, v_players_per_tournament.player_name, v_players_per_tournament.id_tournament
    ORDER BY total_matches_lost DESC;

-- Creates the view that hold total matches won per player per tournament.
CREATE VIEW v_matches_won_per_player_per_tournament AS
    SELECT v_players_per_tournament.id_player, v_players_per_tournament.player_name, v_players_per_tournament.id_tournament, COUNT(t_matches.id_winner) AS total_matches_won
    FROM v_players_per_tournament
    LEFT JOIN t_matches ON v_players_per_tournament.id_player = t_matches.id_winner AND v_players_per_tournament.id_tournament = t_matches.id_tournament
    GROUP BY v_players_per_tournament.id_player, v_players_per_tournament.player_name, v_players_per_tournament.id_tournament
    ORDER BY total_matches_won DESC;

-- Creates the view that hold the final stats per player per tournament.
CREATE VIEW v_total_stats AS
    SELECT v_matches_won_per_player_per_tournament.id_player as id, v_matches_won_per_player_per_tournament.player_name as name,
           v_matches_won_per_player_per_tournament.total_matches_won as wins,
           v_matches_lost_per_player_per_tournament.total_matches_lost as losses,
           v_matches_won_per_player_per_tournament.id_tournament
    FROM v_matches_won_per_player_per_tournament, v_matches_lost_per_player_per_tournament
    WHERE v_matches_won_per_player_per_tournament.id_player = v_matches_lost_per_player_per_tournament.id_player AND v_matches_won_per_player_per_tournament.id_tournament = v_matches_lost_per_player_per_tournament.id_tournament
    GROUP BY v_matches_won_per_player_per_tournament.id_player, v_matches_won_per_player_per_tournament.player_name,
           v_matches_won_per_player_per_tournament.total_matches_won,
           v_matches_lost_per_player_per_tournament.total_matches_lost,
           v_matches_won_per_player_per_tournament.id_tournament;

Select allOpen in new window

I would probably test something like

SELECT PL.id, PL.player_name, MA.id_tournament, 
	SUM(WHEN MA.id_winner=PL.id THEN 1 ELSE 0 END) AS total_matches_won,
	SUM(WHEN MA.id_loser=PL.id THEN 1 ELSE 0 END) AS total_matches_lost
FROM t_players AS PL
LEFT JOIN t_tournament_players AS TOPL 
  ON (TOPL.id_player=PL.id)
LEFT JOIN t_matches MA
  ON (TOPL.id_tournament=MA.id_tournament)
GROUP BY PL.id, PL.player_name, MA.id_tournament
  ORDER BY total_matches_won DESC;
  

Select allOpen in new window

Since for each players all her/his matches will be collected, instead of counting won and lost you might count total number of matches and won, lost being the difference.

Test and edit the query (no easy way for me to debug it), then you can convert into a view.

I presume you are using phppgadmin from a web page? If not, install it since It would make your life a lot easier
http://phppgadmin.sourceforge.net/

Hello there and thanks for your kind help...

I've tried it and I get an error:

tournament=> SELECT PL.id, PL.player_name, MA.id_tournament,
tournament-> SUM(WHEN MA.id_winner=PL.id THEN 1 ELSE 0 END) AS total_matches_won,
tournament-> SUM(WHEN MA.id_loser=PL.id THEN 1 ELSE 0 END) AS total_matches_lost
tournament-> FROM t_players AS PL
tournament-> LEFT JOIN t_tournament_players AS TOPL
tournament->   ON (TOPL.id_player=PL.id)
tournament-> LEFT JOIN t_matches MA
tournament->   ON (TOPL.id_tournament=MA.id_tournament)
tournament-> GROUP BY PL.id, PL.player_name, MA.id_tournament
tournament->   ORDER BY total_matches_won DESC;
ERROR:  syntax error at or near "WHEN"
LINE 2: SUM(WHEN MA.id_winner=PL.id THEN 1 ELSE 0 END) AS total_matc...

Select allOpen in new window

Maybe instead of WHEN you wanted to use CASE ?

I've been fiddling a bit with your code to make it work and in the end this is a working version:

SELECT t_players.id, t_players.name, t_matches.id_tournament, 
    SUM(CASE WHEN t_matches.id_winner=t_players.id THEN 1 ELSE 0 END) AS total_matches_won,
    SUM(CASE WHEN t_matches.id_loser=t_players.id THEN 1 ELSE 0 END) AS total_matches_lost
FROM t_players
LEFT JOIN t_tournaments_players 
  ON (t_tournaments_players.id_player=t_players.id)
LEFT JOIN t_matches
  ON (t_tournaments_players.id_tournament=t_matches.id_tournament)
GROUP BY t_players.id, t_players.name, t_matches.id_tournament
  ORDER BY total_matches_won DESC;

Select allOpen in new window

Problem is that I get "holes" in the result:

 id |  name   | id_tournament | total_matches_won | total_matches_lost
----+---------+---------------+-------------------+--------------------
  2 | Player2 |             1 |                 1 |                  0
  3 | Player3 |             1 |                 0 |                  0
  4 | Player4 |             1 |                 0 |                  0
  5 | Player5 |             1 |                 0 |                  0
  5 | Player5 |               |                 0 |                  0
  6 | Player6 |             1 |                 0 |                  0
  1 | Player1 |             1 |                 0 |                  1
  6 | Player6 |               |                 0 |                  0

Select allOpen in new window

Just to be sure: Canyou post your data tables?

But I simply input random data into the tables...

Do you want an export?

Yes, we need to get your data sample to check if the result are OK or not

Can you provide is with the dataset used to check your query?
It's difficult to know if the query is right or wrong without the data sample which was queried.

B-) Glad to help. Thx for the grade and points