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;
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