CODEWITHSUNDEEP
mysqljoin
hearmeroar
hearmeroar

Reputation: 307

Joining Two Tables in MySQL with diff column name

I'm not very good at joining tables in mysql and I'm still learning, So I wanted to ask, when joining two tables....

I have 2 tables So for the first table I want to join the 2 of its columns (id & path) on the second table.

But on the second table there's no column name id and path, there is a column name pathid & value. The field of the pathid column is the same as the id.

it looks like this.

first table

| id | path        | 
---------------------
|  1 | country/usa |     
|  2 | country/jpn |    
|  3 | country/kor |

second table

| pathid | value  |
-------------------
|  3     | 500    |    
|  1     | 10000  |    
|  2     | 2000   |

So on the first table, it indicates that for usa the id is 1, japan is 2, korea is 3. And on the table it says that for pathid no. 3 ( which is the id for korea) the value is 500 and so on with the others.

I want it to look like this. So then the path will be joined on the second table on its corresponding value. How can I do this on mysql? Thank You

Desired Result

| id | path         | value  |
------------------------------
|  1 | country/usa  | 10000  |    
|  2 | country/jpn  | 2000   |    
|  3 | country/kor  | 500    |

Upvotes: 1

Views: 5215

Answers (1)

Praveen Lobo
Praveen Lobo

Reputation: 7187

You can join on the columns irrespective of the column name as long as the data type match. 

SELECT id, path, value
FROM firstTable, secondTable
WHERE id = pathid

If you have same column names on both tables then you need to qualify the name using alias. Say the column names for id were same on both tables then whenever you use id you should mention which table you are referring to. other wise it will complain about the ambiguity. 

SELECT s.id, path, value
FROM firstTable f, secondTable s
WHERE f.id = s.pathid

Note that I ommited s. on other columns in select, it will work as long as the second table doesn't have columns with same name.

Upvotes: 4

Related Questions