CODEWITHSUNDEEP
mysqlcolumn-alias
jacekn
jacekn

Reputation: 1541

How to produce query results with automatic table aliases in MySQL?

MySQL ver 10 (MariaDB). PHP 5.6.3 libmysql 5.1.73

It's been a while for me working with Oracle but I vaguely remember that Oracle did exactly what I'm expecting in this example. I could be mistaken or maybe MySQL just isn't doing the same thing... The example is created for this question, so if you see a syntax issue, it's related to that.

Assuming a simple schema like this:

Table COUNTRY
ID
NM

Table PROVINCE
ID
NM
CTID

I was hoping that this query:

SELECT * FROM PROVINCE P JOIN COUNTRY C ON C.ID = P.CTID

Would produce the following output:

P.ID | P.NM | P.CTID | C.ID | C.NM

Unfortunately, the output is without table aliases and columns from joined table that are in the selected table are missing from results (only one ID column in results). Like this:

ID | NM | CTID

Is there a way to get the aliased output shown above? Or is there some other way to get all five columns in the results without having to use anything like P.ID as P_ID explicitly in the query?

Upvotes: 4

Views: 1332

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562891

MySQL does not create qualified aliases like that. If you don't explicitly name aliases, you will have duplicate column names in the result, if the select-list includes columns with the same name in multiple tables.

You don't necessarily have to make aliases for all the columns, only the ones you need to differentiate.

You don't have to forego the wildcard, but you should limit the wildcard to specific tables, for which you don't need to make aliases.

SELECT C.*, P.ID AS P_ID, P.NM AS P_NM
FROM PROVINCE P JOIN COUNTRY C ON C.ID = P.CTID

Upvotes: 1

Related Questions