SELECT * query, excluding columns

Question

I have done allot of reading about this and it seems that isn't possible. The thing is, since the amount of columns is dynamic i cant explicitly mention them. I put in aliases but the 'id' column keeps on appearing.

The CREATE doesnt work since there is 3 id columns in there, obiously.

(This query actually is ok, it works so dont evaluate it please)

CREATE TABLE merged as 
    SELECT *, 
           wuwebusers.id as wuwebusers_id, 
           jgwebusers_address.id as jgwebusers_address_id, 
           jgwebusers.id as jgwebusers_id 
    FROM wuwebusers 
    LEFT OUTER JOIN jgwebusers_address ON wuwebusers.id = jgwebusers_address.userid 
    LEFT OUTER JOIN jgwebusers ON wuwebusers.id = jgwebusers.userid 
    GROUP BY wuwebusers.id

EDIT: question: how can i select every column except a few explicit ones. I know SELECT * isn't the correct way, since i am selecting all. I am asking for other way(s).

Since i have to do this a few times and the columns are dynamic and quite a few it isn't maintainable by explicitly writing every column name.

Do you have a question here? Don't use `select *` you should always specify what columns you want included in your query. — Taryn, Oct 22 '13 at 15:33
Have a look at this one http://stackoverflow.com/questions/413819/select-except. Although they do not advise this there is an answer for you — The Humble Rat, Oct 22 '13 at 15:37
The amount of columns is dynamic? This is probably a bad idea. — Strawberry, Oct 22 '13 at 15:41
Are the columns dynamic in every table and how are they dynamic? They do not currently appear to be. — Zane, Oct 22 '13 at 15:51
Lets just say, they arnt dynamic for now, there are just alot of them and when i say alot, i mean A-LOT! — San Jay Falcon, Oct 22 '13 at 15:53
Script them as `select to` and then copy paste. There are ways around this however with multiple columns being named identically * isn't going to work. — Zane, Oct 22 '13 at 15:55
@Zane: this sounds something to go with.. will give it a try. — San Jay Falcon, Oct 22 '13 at 15:59

Zane · Answer 1 · 2013-10-22T15:57:45.997

First you are doing a SELECT * Then adding columns as well. * is everything therefore you are getting mulitples of the same column. Those 3 ID's that you gave distinct names are being SELECTed again by the * in your query. Remove the * and type in the columns you need and you'll be fine.

CREATE TABLE merged as 
SELECT 
    wuwebusers.id as wuwebusers_id, 
    jgwebusers_address.id as jgwebusers_address_id, 
    jgwebusers.id as jgwebusers_id , 
    AllotherColumnsYouNeed as WhateverYouWantToCallthem
FROM wuwebusers 
LEFT OUTER JOIN jgwebusers_address 
    ON wuwebusers.id = jgwebusers_address.userid 
LEFT OUTER JOIN jgwebusers 
    ON wuwebusers.id = jgwebusers.userid 
    GROUP BY wuwebusers.id

/Edit\ If you changed your tables to not use the generic method of just labling a column ID in the first place you would also solve this problem. If you have a table name product for example call the ID column for that table ProductID. This will also solve your problem.

OP states columns are dynamic and cannot explicitly name them. this wont work or at least will not fulfill the requirement. — Fred, Oct 22 '13 at 15:45

SELECT * query, excluding columns

1 Answers1