Alias for column name on a SELECT * join

Question

I have two tables - client and person. They are linked by personID in both tables.

I am trying to create a view.

I tried:

SELECT * FROM client INNER JOIN person ON client.personID = person.personID

It didn't like it saying Duplicate column name 'personID.

I thought if you used table.column that was fine as an identifier. I tried with and without single quotes.

I could do an alias if I wanted specific columns EG client.personID AS perID but I cannot work out how to make an alias for the columns in this position IE when they are part of the JOIN definition.

I have been looking and trying for over an hour and just cannot find it.

EDIT: This is a question SPECIFICALLY about aliasing columns in a SELECT * join. Everyone seems to be saying put in the explicit columns. I know how to do that. I deliberately want to use a * JOIN for development work. This will be a tiny DB so it does even not matter if it stays in. Oh and this is only a problem storing as a VIEW not as an SQL query or in a bit of code.

Answer 1

It looks like you have to alias you'r column names with aliases.

SELECT client.column1 as col1, client.column2 as col2, person.column1 as colp1 FROM client INNER JOIN person ON client.personID = person.personID

Of course, replace the column names into the real column names as use more appealing aliases

Let us know if it helps

UPDATE #1

I tried creating 2 tables with sqlfiddle in mySQL 5.5 and 5.6

see link : http://sqlfiddle.com/#!9/e70ab/1

It works as expected.

Maybe you could share you tables schema.

Here's the example code :

CREATE TABLE Person
(
personID int,
  name varchar(255)
);

CREATE TABLE Client
(
ID int,
  name varchar(255),
personID int
);

insert into Person values(1, 'person1');
insert into Person values(2, 'person2');
insert into Person values(3, 'person3');

insert into Client values(1, 'client1', 1);
insert into Client values(2, 'client2', 1);
insert into Client values(3, 'client1', 1);

SELECT * FROM client 
INNER JOIN person 
ON client.personID = person.personID;

Answer 2

SELECT
        c.*,
        p.`all`,
        p.need,
        p.`fields`,
        p.`of`,
        p.person,
        p.`table`,
        p.without,
        p.personID_field
    FROM client c
    INNER JOIN person p
        ON p.personID = c.personID