How to use Inner join on the same detail table repeatedly
I am trying to use TWICE inner join statement to get reference values in the same detail table.
Master table: bags_tbl
ID ... bagA bagB
1 ... 121 122
2 ... 123 124
3 ... 125 126
Detail table: fruit_tbl
ID ... fruit ...
121 strawbery
122 apple
123 orange
124 raspberry
125 pear
126 pineapple
SELECT
bags_tbl.ID,
bags_tbl.A,
ba开发者_JS百科gs_tbl.B,
fruit_tbl.fruit AS bagA_fruit,
fruit_tbl.fruit AS bagB_fruit
FROM
bags_tbl
Inner Join fruit_tbl ON bags_tbl.bagA = fruit_tbl.fruit
Inner Join fruit_tbl ON bags_tbl.bagB = fruit_tbl.fruit
this throw error : no unique table/alias... How to make SQL statement to get text representation of master table?
thankx a lot
SELECT
bags_tbl.ID,
bags_tbl.A,
bags_tbl.B,
A.fruit AS bagA_fruit,
B.fruit AS bagB_fruit
FROM bags_tbl
Inner Join fruit_tbl A ON bags_tbl.bagA = A.id
Inner Join fruit_tbl B ON bags_tbl.bagB = B.id
Try this. You need to grant a unique alias to each join so SQL knows which one you're referencing in the SELECT clause.
SELECT
bags_tbl.ID,
bags_tbl.A,
bags_tbl.B,
fruitA.fruit AS bagA_fruit,
fruitB.fruit AS bagB_fruit
FROM
bags_tbl
JOIN fruit_tbl fruitA ON bags_tbl.bagA = fruitA.id
JOIN fruit_tbl fruitB ON bags_tbl.bagB = fruitB.id
精彩评论