CODEWITHSUNDEEP
phpmysqljoininner-join
TJR
TJR

Reputation: 6577

Rename for column names in inner Join

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  |  item_1  |   item_2  |  name_1   |  name_2 |
    ----------------------------------------------------
    |  1   |   1      |    2      | item_1 |  item_2   | 
    ----------------------------------------------------

Upvotes: 3

Views: 7836

Answers (3)

AKDADEVIL
AKDADEVIL

Reputation: 206

Despite your table design being pretty meh, you can join a table multiple times by using aliases.

SELECT c.*, 
       i1.name as name1, 
       i2.name as name2 
FROM (c INNER JOIN i AS i1 ON c.item1=i1.id_i) 
     INNER join i as i2 ON c.item2=i2.id_i

But please reconsider the table design, it is a simple 1:n-relationship between c and i!

Upvotes: 1

Rajeev Ranjan
Rajeev Ranjan

Reputation: 4142

this serves the same 

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

Upvotes: 1

Mark Baker
Mark Baker

Reputation: 212412

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

Upvotes: 5

Related Questions