CODEWITHSUNDEEP
sql-server
Chris Rutherford
Chris Rutherford

Reputation: 1672

Select all from Table Set one column to alias with AS

I have a query that selects all from about five tables and joins them all together. The issue is that each of the tables has two column names that are the same across all the tables. I want to select all the data, but I want to tie these two columns in as a different name than what is given to them.

My Query looks like this currently:

select updatedInfo.*,
                nameInfo.*,
                addressInfo.*,
                phoneInfo.*,
                emailInfo.*,
                positionInfo.*
                from updatedInfo join nameInfo on updatedInfo.IndivId=nameInfo.nameInfoId
                      join addressInfo on updatedInfo.IndivId=addressInfo.addressInfoId
                      join emailInfo on updatedInfo.IndivId=emailInfo.emailInfoId
                      join phoneInfo on updatedInfo.IndivId=phoneInfo.phoneInfoId
                      join positionInfo on updatedInfo.IndivId=positionInfo.IndivId
                      where updatedInfo.correctedInFNV is not null
                      order by updatedInfo.IndivId

The column names are updated and alreadyCorrect. Updated notes that the information has been updated in some way, and AlreadyCorrect denotes that the information was correct when sent for update.

Can I do something like this?

select updatedInfo.*,
       nameInfo.*, nameInfo.updated as nameUpdated .....

would that work? or would I have to list every column on the table? Would this take both the un-renamed column AND the aliased one?

Upvotes: 2

Views: 2765

Answers (1)

Roggan29
Roggan29

Reputation: 56

Your alternative would not work as you would still have the old column name in it (as you are selecting with * ). In result you would have the "Updated" column twice. Once with the name "Updated" and once with "nameUpdated".

The only solution is to list every column and give them an alias if necessary.

Alternative: You can create a temporary/hash table where you give your column a different name, then using the select from your question to insert all the data and in the end you just do Select * from tempTable

Upvotes: 4

Related Questions