Left outer join make duplicate column name

Question

Left outer join make duplicate column name

adonetok

SSCrazy Eights

Points: 8395
More actions
August 23, 2017 at 1:55 pm

#402436

I used code below to select 10 temp tables as one row table.
All 10 tables has the same column [FileID].
How to make final table only list one [FileID] column?
SELECT DISTINCT
   *
FROM #1 A01
   LEFT OUTER JOIN #2 A2
    ON A01.FileId = A2.FileId
   LEFT OUTER JOIN #3 A3
    ON A01.FileId = A3.FileId
   LEFT OUTER JOIN #4 A4
    ON A01.FileId = A4.FileId
   LEFT OUTER JOIN #5 A5
    ON A01.FileId = A5.FileId
   LEFT OUTER JOIN #6 A6
    ON A01.FileId = A6.FileId
   LEFT OUTER JOIN #7 A7
    ON A01.FileId = A7.FileId
   LEFT OUTER JOIN #8 A8
    ON A01.FileId = A8.FileId
   LEFT OUTER JOIN #9 A9
    ON A01.FileId = A9.FileId

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

Should be fairly straightforward considering you have aliases on each table, what have you tried and what problem were you having?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Basically, you need to explicit list the columns you want returned from the tables instead of using the *.

adonetok SSCrazy Eights Points: 8395 More actions · Answer 3

One stored procedure will display more than 10 tables in which each table has more than 30 columns and some columns' name is the same.
I try to modify it to display as only one table.
It will take a lot time to list all columns in each table.

Ant-Green SSC Guru Points: 113507 More actions · Answer 4

That is where tools like Red-Gates SQL Prompt come in handy to code refactoring.

You could also look at the sys.columns or information_schema for the columns in the tables in question and pull them out on bulk for each table and refactor that way.

But that's the joys of code refactoring.

adonetok SSCrazy Eights Points: 8395 More actions · Answer 5

Our company just purchased Red-Gates SQL Prompt.
How to use code refactoring for this issue?
I checked menu but didn't find out.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Put cursor next to the * in the SELECT. Press TAB. Remove columns you don't want.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

adonetok SSCrazy Eights Points: 8395 More actions · Answer 7

adonetok

SSCrazy Eights

Points: 8395

August 24, 2017 at 6:29 am

#1956597

Great!
Thank you, GilaMonster!
You save my life.

anand08sharma SSC Enthusiast Points: 180 More actions · Answer 8

adonetok - Wednesday, August 23, 2017 1:55 PM
I used code below to select 10 temp tables as one row table.
All 10 tables has the same column [FileID].
How to make final table only list one [FileID] column?
SELECT DISTINCT
   *
FROM #1 A01
   LEFT OUTER JOIN #2 A2
    ON A01.FileId = A2.FileId
   LEFT OUTER JOIN #3 A3
    ON A01.FileId = A3.FileId
   LEFT OUTER JOIN #4 A4
    ON A01.FileId = A4.FileId
   LEFT OUTER JOIN #5 A5
    ON A01.FileId = A5.FileId
   LEFT OUTER JOIN #6 A6
    ON A01.FileId = A6.FileId
   LEFT OUTER JOIN #7 A7
    ON A01.FileId = A7.FileId
   LEFT OUTER JOIN #8 A8
    ON A01.FileId = A8.FileId
   LEFT OUTER JOIN #9 A9
    ON A01.FileId = A9.FileId

Specify the Column instead of using , Select Distinct * from as there can be duplicate Field in master table on which you are create a join to other tables

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 9

adonetok - Wednesday, August 23, 2017 1:55 PM

Based on almost 40 years with RDBMS, I smelled something really bad here. First of all, the fact that the common column in all of these temporary tables (also known as scratch tapes back in the days before RDBMS) is called "field" because columns are not like fields. A totally different concept! What is the meaning of each of these temporary tables? In a valid data model, they will all be totally separate, independent sets of entities. On the other hand, if you really screwed up everything in each one will represent an attribute (attributes are not entities) of some common entity. And this would be an example of attributes splitting, a design flaw where the parts of an attribute are put in tables, instead of in columns. Think about someone who so bad off they actually put shoe size, hat size, IQ, etc. their own tables for the poor same guy they were trying to model. Can you please explain what you're doing.

Yes we give you a kludge and show you how to patch of your mess. But would you rather do this correctly?

Please post DDL and follow ANSI/ISO standards when asking for help.