3

I have 3 tables (mysql db) on which I want to do an inner join. The problem is that these tables have common columns. I know I could use aliases to differentiate them (select A.x as Ax, B.x as Bx, C.c as Cx ...) but this requires to list the columns. However it is not really an option. Is there a way I could give an alias to all the columns of a table at once?

Something like select a.* as a*?

Improve this question
6
  • 2
    You should always give the columns list regardless. But yes, a.* should work (without a possibility for column aliases), and you could as well try it yourself... – dezso Feb 21 '14 at 10:36
  • Yes thanks, I know it works and I did try it myself, but you are not answering my question. – Chris Feb 25 '14 at 6:58
  • This is why I left it in a comment and not an answer. However, I would like to know why listing the columns is not an option... – dezso Feb 25 '14 at 9:11
  • 1
    Because it is kind of an ORM, that we want as agnostic to the content as possible. But anyway, I got around by using introspection on my objects. Thanks! – Chris Feb 26 '14 at 10:18
  • One approach can be to add prefixes to the column, derived from the table name, like a first name column on a table calles order, gets the name o_first_name, for example. If you manage to keep the prefixes unique, it can solve such a problem. – dezso Feb 26 '14 at 10:45
4

You need to specify every column by your self.

Example : Table Country and City both have a name column

SELECT 
    a.Name AS CountryNane, b.Name AS CityName, c.*
FROM
    Country a
        JOIN
    City b ON a.Code = b.CountryCode
        JOIN
    CountryLanguage c ON a.Code = c.CountryCode;
Improve this answer
1

If you want to assign an alias to a field you must explicit that field in the list fields of SELECT statement.

So, if you have common name field in your three tables, you must explicit.

If you use a notation like this SELECT a.* ... you'll show all fields of your aliased table a but the name of field in the header column is the same of the table structure.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.