Your problem is the use of SELECT * You need to be specific about which columns you are selecting from macro_lookup and what names you want to give them. Since you have not provided us with the full structure, I can only suggest something like
CREATE OR REPLACE VIEW sample_macro AS SELECT sample.col1 as viewcol1 m1.col1 as viewcol2, m1.col2 as viewcol3, m2.col4 as viewcol4, m3.col7 as viewcol5 FROM sample LEFT JOIN macroscopic ON query_srpnt_sample.samp_id = macroscopic.sample_id LEFT JOIN macro_lookup AS ml1 ON ml1.macro_lookup_id = macroscopic.grain_id LEFT JOIN macro_lookup AS ml2 ON ml2.macro_lookup_id = macroscopic.lustre_id LEFT JOIN macro_lookup AS ml3 ON ml3.macro_lookup_id = macroscopic.translucency_id WHERE samp_id is not null; On Tue, Nov 17, 2015 at 10:10 AM, Killian Driscoll < killiandrisc...@gmail.com> wrote: > I have a view with 15 columns and want to create another view based on a > join with another table with 15 columns that includes three columns that > reference one lookup table. > > If I use the the below sql I get the error "column "macro_lookup_id" > specified more than once". I have read that I can rename the columns (I > renamed the tables as ml1, ml2, ml3) but can't figure out how to do this > but also use the select * to avoid writing out all rest of the column names. > > CREATE OR REPLACE VIEW sample_macro AS > SELECT * > FROM sample > LEFT JOIN macroscopic ON query_srpnt_sample.samp_id = macroscopic.sample_id > LEFT JOIN macro_lookup AS ml1 ON ml1.macro_lookup_id = macroscopic.grain_id > LEFT JOIN macro_lookup AS ml2 ON ml2.macro_lookup_id = > macroscopic.lustre_id > LEFT JOIN macro_lookup AS ml3 ON ml3.macro_lookup_id = > macroscopic.translucency_id > WHERE samp_id is not null; > > What is the most efficient way (in terms of typing out column names) to > create this type of view? > > > Killian DriscoIl > IRC Postdoctoral Fellow > UCD School of Archaeology > University College Dublin > > academia.edu/KillianDriscoll <https://ucd.academia.edu/KillianDriscoll> > www.lithicsireland.ie > ca.linkedin.com/in/killiandriscoll > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.