Problem with learning MANY to MANY relation in SQL

Hi everyone!

I am studying to learn how to build many to many into postgre sql but I am quite stuck.

The example I work on is the classic players / tournaments.

I've made my database this way:

http://pastebin.com/eTAa74T2

And I've tried to write my views without success (because I can't put id_tournament into it):

http://pastebin.com/Dn0U1nAN


I'm not necessarily for a complete solution: even a good hint to understand would be very welcome...
LVL 1
ltpittAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bernard S.CTOCommented:
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?
0
ltpittAuthor Commented:
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;

Open in new window

0
ltpittAuthor Commented:
Ps

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

Is this possible?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Bernard S.CTOCommented:
(your first pastebin with data structure has been removed, we need it back)
0
ltpittAuthor Commented:
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;

Open in new window

0
Bernard S.CTOCommented:
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;
  

Open 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/
0
ltpittAuthor Commented:
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...

Open in new window


Maybe instead of WHEN you wanted to use CASE ?
0
Bernard S.CTOCommented:
Oops...right...
SELECT PL.id, PL.player_name, MA.id_tournament, 
	SUM(CASE WHEN MA.id_winner=PL.id THEN 1 ELSE 0 END) AS total_matches_won,
	SUM(CASE 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;

Open in new window

should wotk better!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ltpittAuthor Commented:
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;

Open 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

Open in new window

0
Bernard S.CTOCommented:
Just to be sure: Canyou post your data tables?
0
ltpittAuthor Commented:
But I simply input random data into the tables...

Do you want an export?
0
Bernard S.CTOCommented:
Yes, we need to get your data sample to check if the result are OK or not
0
Bernard S.CTOCommented:
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.
0
Bernard S.CTOCommented:
B-) Glad to help. Thx for the grade and points
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.