MySQL SELECT only 1 field with AS alias

Question

I'd like to select all columns in a table, but give only one of them an alias.

I have this table:

id
base
window
thickness
shading
shape
... and many more columns

Now, I want to select the base with an alias, like so: SELECT base AS structure. But I want to select all the other fields as well, without having to type them all out.

I tried SELECT *, base AS structure, but it gives me an error.

In truth, that's not even really what I want, because I don't want base to show up at all.

Is this at all possible?

FYI, never just say "it gives an error" without following that up with the actual error message — Phil, Aug 02 '11 at 02:00
"it gives me an error" => I give you a stab in the face over the internet. — Lightness Races in Orbit, Aug 02 '11 at 02:12
(FYI, selecting `*` and then again columns from the same table is not allowed.) — Lightness Races in Orbit, Aug 02 '11 at 02:13

score 11 · Answer 1 · answered Aug 02 '11 at 01:39

11

No, it isn't. Suck it up and type them all out :)

answered Aug 02 '11 at 01:39

Tom Studee

10,316
4
38
42

18

Oh wow, 30? Well.. you don't have to do it all at once. Maybe type 10 columns, take a break, catch your breath, type 10 more, etc.. – Tom Studee Aug 02 '11 at 01:44
@Tom: LOL. Obviously, it is not your fault that I'm lazy, but that's not what I meant. It's not laziness. It's just that listing them all out like that is extremely prone to error. Let alone when the table is subsequently `altered`. I'm just baffled that they (MySQL) haven't thought of this to be of interest to anybody. I'm sure I'm not the only one with this problem!! – John MacArthur Aug 02 '11 at 01:59
1

@John In that case, I'd recommend using an ORM. See [Doctrine](http://www.doctrine-project.org/) or [Propel](http://www.propelorm.org/) (assuming PHP) – Phil Aug 02 '11 at 02:04
1

@John: Instead of assuming that the MySQL designers "haven't thought of this", you should instead jump to the (far more likely) conclusion that it is your requirement that is in error. – Lightness Races in Orbit Aug 02 '11 at 02:11
@Phil: I am. I'm using Codeigniter's Active Records class. But I don't see how that's gonna help me here... – John MacArthur Aug 02 '11 at 14:06
@Tomalak: What do you mean by "your requirement is in error". How could I otherwise achieve this? – John MacArthur Aug 02 '11 at 14:08
1

@John: Achieve _what_? What is your wider goal? Why do you feel that you need to alias this field? – Lightness Races in Orbit Aug 02 '11 at 14:14

score 4 · Answer 2 · edited May 23 '17 at 12:00

No.

You either list the ones you want, or you say "all" by writing *.

These are the two options at your disposal.

Laziness: begone! (And, let's face it, if you really need this alias, then your field is probably named wrong in the first place...)

Ultimately, you could create a VIEW to do this job transparently, but then you'd have to keep updating it as you ALTER your original table.

I was trying to avoid bringing this to your attention, but this answer does demonstrate a rounadabout way:

SET @sql = CONCAT('SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), '<columns_to_exclude>,', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<table>' AND TABLE_SCHEMA = '<database>'), ' FROM <table>');

PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

(Replacing <table>, <database> and <columns_to_exclude>).

I wish to re-iterate, though: don't. Something's wrong if you have to do this.

score 3 · Answer 3 · answered Mar 21 '13 at 15:40

3

If you don't mind also having the old name you can do something like this:

SELECT MyTable.*, MyTable.base AS structure

answered Mar 21 '13 at 15:40

user1118279

207
3
4

score 2 · Answer 4 · answered Aug 02 '11 at 01:43

2

maybe there is a better way to solve your problem, at least the following answer works for you if you are not too lazy:

SELECT id, base AS structure, window, thickness, shading, shape ... from yourTable

answered Aug 02 '11 at 01:43

ben

21
2

Which is precisely what I was trying to avoid!! – John MacArthur Aug 02 '11 at 01:56

Finn · Answer 5 · 2012-03-28T03:09:25.427

2

as far as I know (and a check with the MySQL documentation confirmed) that it's not possible to list all the column with the original name except one, at least using *.

edited Mar 28 '12 at 03:09

answered Aug 02 '11 at 01:45

Finn

21
4

score 1 · Answer 6 · edited Sep 28 '12 at 10:01

1

Inefficient, but clean, try left joining the same rows to themselves then selecting from the left join the desired column with its new name.

ie.

SELECT table_1.*, table_2.base AS structure 
SELECT 
    table_1.*, 
    table_2.base AS structure 
FROM 
    table1 AS table_1 
        LEFT JOIN table2 AS table_2 
            ON table_1.id=table_2.id

edited Sep 28 '12 at 10:01

Fluffeh

33,228
16
67
80

answered Aug 06 '12 at 19:23

antago

11
1

score 0 · Answer 7 · edited Dec 10 '19 at 10:18

0

Query:

SELECT firstName AS firstStudent FROM student LIMIT 1;

edited Dec 10 '19 at 10:18

Stephan Hogenboom

1,543
2
17
29

answered Dec 10 '19 at 08:36

MySQL SELECT only 1 field with AS alias

7 Answers7