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;
In abbreviating the names for the post I just realised I confused the names; it should have read.
CREATE OR REPLACE VIEW sample_macro AS SELECT * FROM query_srpnt_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
