MySQL Syntax error or access violation: 1066 Not unique table/alias: 'users'

Question

I am facing difficulties to join three tables. And then joining the third table two times to get user information

I have a table called teams and in this table, I have playerOneId and playerTwoId. These id s are actually from users table so I also want to get both the players information from the users table. So here I tried a query

    select 
    `clubteams`.`id`, `clubteams`.`className`, `clubteams`.`que`, 
    `teams`.`id`, `teams`.`teamName`, `teams`.`playerOneId`, `teams`.`playerTwoId`,
    `users`.`firstName`, `users`.`lastName`, `users`.`email`, `users`.`phone` 
    from `teams` 
    inner join `clubteams` on `teams`.`id` = `clubteams`.`team_id` 
    inner join `users` on `teams`.`playerOneId` = `users`.`id` 
    inner join `users` on `teams`.`playerTwoId` = `users`.`id` 
    where `clubteams`.`club_id` = 9 and `clubteams`.`isTemporary` = 1

Here note that two joins with users table

inner join `users` on `teams`.`playerOneId` = `users`.`id` 
inner join `users` on `teams`.`playerTwoId` = `users`.`id`

how can I get both players information using playerOneId and playerTwoId?

vvvv4d · Accepted Answer · 2020-08-25 14:08:41Z

1

Alias the user tables u1 and u2 to resolve the error.

inner join `users` u1 on `teams`.`playerOneId` = u1.`id` 
inner join `users` u2 on `teams`.`playerTwoId` =  u2.`id`

answered Aug 25, 2020 at 14:08

vvvv4d

4,1351 gold badge18 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

slaakso · Accepted Answer · 2020-08-25 14:15:19Z

1

You can use aliases instead of full table names. Also, no need for backticks unless you use reserved words.

select 
    ct.id, 
    ct.className, 
    ct.que, 
    t.id, 
    t.teamName, 
    t.playerOneId, 
    t.playerTwoId,
    u2.firstName, 
    u2.lastName, 
    u2.email, 
    u2.phone 
from teams t
    inner join clubteams ct on t.id = ct.team_id 
    inner join users u1 on t.playerOneId = u1.id 
    inner join users u2 on t.playerTwoId = u2.id 
where ct.club_id = 9 and ct.isTemporary = 1

answered Aug 25, 2020 at 14:15

slaakso

9,1452 gold badges19 silver badges29 bronze badges

Collectives™ on Stack Overflow

MySQL Syntax error or access violation: 1066 Not unique table/alias: 'users'

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related