driftdrift driftdrift - 1 month ago 19
SQL Question

What is wrong with my PSQL view table?

I have two tables player and match:

CREATE TABLE player(
id serial PRIMARY KEY NOT NULL,
name varchar(255) NOT NULL
);

CREATE TABLE match(
id serial PRIMARY KEY,
winner serial REFERENCES player(id) NOT NULL,
loser serial REFERENCES player(id) NOT NULL CHECK (loser != winner)
);

CREATE SEQUENCE playerid_sequence
start 1
increment 1;

CREATE SEQUENCE matchid_sequence
start 1
increment 1;


I want to create a view table that joins the two tables:

CREATE VIEW matchplayers AS
SELECT winner.name, loser.name, m.id
from player winner, player loser, match m
WHERE m.winner = winner.id AND m.loser = loser.id;


But it is returning an error that "name" has been mentioned more than once. Fairly inexperienced to SQL

Answer

Try

CREATE VIEW matchplayers AS
SELECT winner.name as winner_name, loser.name as loser_name, m.id 
from player winner, player loser, match m
WHERE m.winner = winner.id AND m.loser = loser.id;

to get unambiguous column names of the view.

Comments