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?