CODEWITHSUNDEEP
mysqlsqldatabase
John
John

Reputation: 1433

Is it possible to rename a joined column during an inner join?

Say I have two tables, owner and dog. Both have column name, but I'd like to join them, so there is a problem since both tables have column name. Can I rename (alias) the name column in the dog table during the query?

Upvotes: 56

Views: 104231

Answers (6)

Shivam Bharadwaj
Shivam Bharadwaj

Reputation: 2286

Agents.lastname as AgentName


Where,
Agents = Table Name
lastname = Column Name in table
AgentName = New Column name that you want to add

Example:

SELECT 
    Buyers.id, 
    Buyers.mobile,
    Agents.lastname as AgentName
FROM Buyers 
INNER JOIN Agents ON Buyers.agentId=Agents.id

Upvotes: 1

Ezequiel Moraes
Ezequiel Moraes

Reputation: 41

It is possible and very simple, do your SQL normally and add a comma after the asterisk and use 'AS'.

Before:

SELECT * FROM owner

INNER JOIN dog

ON owner.id = dog.id

After:

SELECT *, dog.name AS dogName FROM owner

INNER JOIN dog

ON owner.id = dog.id

Upvotes: 4

Sanjay
Sanjay

Reputation: 342

You Can Give any Alias name to the column but there is some rules like the alias name must not the key word of the SQL Server, it must not contain space, if you want space then it should be in [] you can not use some symbols for alias.

Ex :- Select owner.Id As [Int], Owner.,dog. From Owner inner join Gog On Owner.Id = Dog.Id

Upvotes: 0

mellamokb
mellamokb

Reputation: 56769

Yes, you can, but then you must list out all of the fields instead of using select *:

    select o.*, d.*
      from owner o
inner join (select dog_id, name as dog_name, breed, age, owner_id from dog) d
    on o.owner_id = d.owner_id

Upvotes: 22

MJB
MJB

Reputation: 7686

Yes, you can rename the columns in the output of a join, that is called an alias. However, the fact that they are the same does not cause any problem; they just need to be fully qualified.

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171381

select d.Name as DogName, o.Name
from Dog d
inner join Owner o on d.OwnerID = o.OwnerID

Upvotes: 73

Related Questions