phpmyadmin Question posted in
The official documentation can be found here.

Is there a way to fetch MySQL records using different ID alias's – Phpmyadmin

I am trying to fetch records using different aliases but they are however related.

The server I’m using is WAMP. I have successfully connected my project/script to the PhpMyAdmin database and some records can be seen on my PHP file using some queries and be successfully inserted.

However, when I try to fetch records using different aliases I get an error stating the following: “MySQL said: #1066 – Not Unique table/alias:’teams'”?

Can you please explain whats going on because on the database when I check the ID’s of these records, they show that they are connected/pulling the record from another table.

The following is my MySQL query in which I tried to alter several times but no success came about after every attempt:

SELECT DISTINCT fixtures.fixture_id, fixtures.fixture_date,
        fixtures.fixture_time, fixtures.home_teamID, 
        fixtures.away_teamID, fixtures.comp_id 
From fixtures 
    JOIN teams ON fixtures.home_teamID = teams.team_id 
    JOIN teams ON fixtures.away_teamID = teams.team_id 
    JOIN competitions ON fixtures.comp_id = competitions.comp_id 
ORDER BY fixture.id ASC

The Error Message from the query states that:

SELECT DISTINCT fixtures.fixture_id, fixtures.fixture_date, 
        fixtures.fixture_time, fixtures.home_teamID, 
        fixtures.away_teamID, fixtures.comp_id 
From fixtures 
    JOIN teams ON fixtures.home_teamID = teams.team_id 
    JOIN teams ON fixtures.away_teamID = teams.team_id 
    JOIN competitions ON fixtures.comp_id = competitions.comp_id 
ORDER BY fixture.id ASC 
LIMIT 0, 25

Please explain to me what am I doing wrong and please note I am new to this platform using PHP and MySQL. Thanks in advance.

Tags:

2

Answers


  1. 0

    If you want join two ime the same table you need alias 

      SELECT DISTINCT fixtures.fixture_id
        , fixtures.fixture_date
        , fixtures.fixture_time
        , fixtures.home_teamID
        , fixtures.away_teamID
        , fixtures.comp_id 
        , a.team_name home_team
        , b.team_name  away_team
  From fixtures 
      JOIN teams  a ON fixtures.home_teamID = a.team_id 
      JOIN teams  b ON fixtures.away_teamID = b.team_id 
      JOIN competitions ON fixtures.comp_id = competitions.comp_id 
  ORDER BY fixture.id ASC

    in this sample a and b are the alias for the same table used in two way one for home_team and one for way_team

    and for a more compact code as suggested by @Tim Biegeleisen you can use alias as 

      SELECT DISTINCT f.fixture_id
        , f.fixture_date
        , f.fixture_time
        , f.home_teamID
        , f.away_teamID
        , f.comp_id 
        , a.team_name home_team
        , b.team_name  away_team
  From fixtures f 
      JOIN teams  a ON f.home_teamID = a.team_id 
      JOIN teams  b ON f.away_teamID = b.team_id 
      JOIN competitions c  ON f.comp_id = c.comp_id 
  ORDER BY f.id ASC
    Login or Signup to reply.
  2. 0

    You’re joining ‘teams’ two times, so when you refer to ‘teams’ it doesn’t know whether you mean the first teams that was joined on home_teamID or the second one that was joined on away_teamID.

    You fix this by giving each join a different name like so:

    SELECT DISTINCT fixtures.fixture_id, fixtures.fixture_date,
        fixtures.fixture_time, fixtures.home_teamID, 
        fixtures.away_teamID, fixtures.comp_id 
From fixtures 
    JOIN teams AS hometeams ON fixtures.home_teamID = hometeams.team_id 
    JOIN teams AS awayteams ON fixtures.away_teamID = awayteams.team_id 
    JOIN competitions ON fixtures.comp_id = competitions.comp_id 
ORDER BY fixture.id ASC
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search