mysql - How to join same column twice? -

this question has answer here:

• how join on same table, twice, in mysql? 3 answers

i'm building trading site , have these tables

example:

the first 1 fruit.

+----+--------+ | id |  name  | +----+--------+ |  1 | apple  | |  2 | orange | |  3 | banana | +----+--------+ 

the second 1 trading

+-----+-------+--------+-------+ | tid | first | second | third | +-----+-------+--------+-------+ |   1 |     2 |      1 |     3 | |   2 |     3 |      1 |     2 | +-----+-------+--------+-------+ 

i want join them table show whats first/second/third means , like

+-----+-------+--------+--------+-------+-------+--------+ | tid | first |   n1   | second |  n2   | third |   n3   | +-----+-------+--------+--------+-------+-------+--------+ |   1 |     2 | orange |      1 | apple |     3 | banana | |   2 |     3 | banana |      1 | apple |     2 | orange | +-----+-------+--------+--------+-------+-------+--------+ 

here sql

select trade.tid ,trade.first , fruit.name n1,trade.second ,      fruit.name n2,trade.third , fruit.name n3 trade  inner join fruit  on trade.first=fruit.id inner join fruit  on trade.second=fruit.id inner join fruit  on trade.third=fruit.id 

and got response

error code: 1066. not unique table/alias: 'card' 

what should ? sql wrong or database should not store data ?

you need use aliases on tables. idea, , necessary when have same table multiple times in from:

select t.tid, t.first, f1.name n1, t.second, f2.name n2,        t.third, f3.name n3 trade t left join      fruit f1      on t.first = f1.id left join      fruit f2       on t.second = f2.id left join      fruit f3      on t.third = f3.id; 

notice changed inner join left join. handy in case of rows missing "fruit" values. also, table aliases abbreviations of table names, making query easier write , read.