Rename for column names in inner Join

Question

I have 2 tables like this: ``` Table 'c' | id | item_1 | item_2 | ------------------------------- | 1 | 1 | 2 | ------------------------------- Table 'i' | id | name | ------------------- | 1 | item | ------------------- | 2 | item_2 | ------------------- ``` No i need to get from table c the result included the names of the items. so i build an inner JOIN for this. ``` SELECT c.*, i.name FROM c INNER JOIN i ON c.item_1 = i.id OR c.item_2 = i.id ``` This works "fine". But i get something like this back: ``` | id | item_1 | item_2 | name | ---------------------------------------- | 1 | 1 | 2 | item_2 | ---------------------------------------- ``` The problem is clear - i need to get back 2 names for the two item_ids, but only get one. So is it possible to set a new name for the column ? something like this: ``` SELECT c.*, i.name FROM c INNER JOIN i ON c.item_1 = i.id SET name AS name_1 INNER JOIN i ON c.item_2 = i.id SET name AS name_2 ``` So the result will be like this: ``` | id | i…

Accepted Answer

Something like?

```
SELECT c.*, 
       i1.name as name_1, 
       i2.name as name_2
  FROM c 
  INNER JOIN I as i1 
     ON c.item_1 = i1.id 
  INNER JOIN I as i2 
     ON c.item_2 = i2.id 
```

Rename for column names in inner Join

Solution

Problem