Highlighted

Querying same column twice in 1 SQL-statement

New Here ,
Feb 14, 2018

Copy link to clipboard

Copied

Table "teams":

col 1: team_id (int, 4)

col 2: team_name (varchar, 60)

Table "fixtures":

col 1: date (datetime)

col 2: season (varchar, 9)

col 3: series (varchar, 5)

col 4: home_team_id (int, 4)

col 5: away_team_id (int, 4)

I want to select all fixtures and instead of displaying only the team_id for every fixture, I want to show the team_name. I've tried this one with a JOIN-statement but I can only select 1 team_name at a time instead of the team_name of both home & away team. Thus far, I only get to:

SELECT

fixtures.date,

fixtures.home_team,

fixtures.away_team

FROM fixtures

JOIN teams ON fixtures.home_team_id = teams.team_id

WHERE fixtures.season = '2018-2019' AND fixtures.series = 'serie-a'

ORDER BY fixtures.date ASC

When I try to add a second JOIN to the query, it returns the error message that I have a duplicate entry for teams.team_id which I understand to some degree, however, not finding a way around that. Been juggling around with UNION ALL but that one does not bring me even closer though it should be.

EDIT: I'm (trying) to output results via PDO in PHP 7.

TOPICS
Server side applications

Views

553

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Querying same column twice in 1 SQL-statement

New Here ,
Feb 14, 2018

Copy link to clipboard

Copied

Table "teams":

col 1: team_id (int, 4)

col 2: team_name (varchar, 60)

Table "fixtures":

col 1: date (datetime)

col 2: season (varchar, 9)

col 3: series (varchar, 5)

col 4: home_team_id (int, 4)

col 5: away_team_id (int, 4)

I want to select all fixtures and instead of displaying only the team_id for every fixture, I want to show the team_name. I've tried this one with a JOIN-statement but I can only select 1 team_name at a time instead of the team_name of both home & away team. Thus far, I only get to:

SELECT

fixtures.date,

fixtures.home_team,

fixtures.away_team

FROM fixtures

JOIN teams ON fixtures.home_team_id = teams.team_id

WHERE fixtures.season = '2018-2019' AND fixtures.series = 'serie-a'

ORDER BY fixtures.date ASC

When I try to add a second JOIN to the query, it returns the error message that I have a duplicate entry for teams.team_id which I understand to some degree, however, not finding a way around that. Been juggling around with UNION ALL but that one does not bring me even closer though it should be.

EDIT: I'm (trying) to output results via PDO in PHP 7.

TOPICS
Server side applications

Views

554

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Feb 14, 2018 0

Have something to add?

Join the conversation