CODEWITHSUNDEEP
mysql
Hkm Sadek
Hkm Sadek

Reputation: 3209

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

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?

Upvotes: 0

Views: 1875

Answers (2)

slaakso
slaakso

Reputation: 9050

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

Upvotes: 1

vvvv4d
vvvv4d

Reputation: 4095

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`

Upvotes: 1

Related Questions