CODEWITHSUNDEEP
mysqlsqljoinalias
BeNice
BeNice

Reputation: 2305

Alias for column name on a SELECT * join

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.

Upvotes: 4

Views: 9488

Answers (4)

Lukas Eder
Lukas Eder

Reputation: 220902

What is the problem

In SQL, top-level SELECT statements are allowed to produce the same column name twice. Any form of nested SELECT, derived table, or view must produce unique column names. This is why joining two tables and simply selecting all the columns from the tables can produce issues in a view.

Often, people will resort to prefixing all columns in views via well-established prefix naming conventions, e.g.

CREATE VIEW v_client_person AS
SELECT
  client.clientID AS clie_clientID,
  client.name     AS clie_name,
  client.personID AS clie_personID,
  person.personID AS pers_personID,
  person.name     AS pers_name
FROM ...

This is a good idea because you will be easily able to disambiguate any column from such a view.

A workaround

If personID is the only conflicting column between your two tables, then you could use JOIN .. USING as a workaround:

CREATE VIEW v_client_person AS
SELECT * FROM client INNER JOIN person USING (personID)

USING has the nice effect in most databases, including MySQL, to produce the join column only once in the resulting records, independently of what table it originated.

A note on SELECT * in general

In general, however, you shouldn't use SELECT * in a view, but explicitly list and name all your columns as suggested by wmehanna or in my previous note. There are several reasons why you should omit SELECT *:

  • In top-level selects: Performance (this might not be such a problem in nested selects, if the top level select doesn't select all the columns of the nested select)
  • In views (including derived tables): Your view definition depends on the storage order of columns, as well as their names. When the table column changes, your view definition is stale and the behaviour is somewhat undefined. It's generally a good idea to keep in control of your query output, which is why SELECT * is not necessarily a good idea.

Upvotes: 1

wmehanna
wmehanna

Reputation: 394

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;

Upvotes: 5

Deep
Deep

Reputation: 2512

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

Upvotes: 1

Gurvinder Singh
Gurvinder Singh

Reputation: 111

Try specifying all the column names that you want to show instead of using * with listing the column names along with table names that are common to both tables like don't say personID, type client.personID

Upvotes: 0

Related Questions