Re: Querying same lookup table with multiple columns based on another view

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
www.lithicsireland.ie
ca.linkedin.com/in/killiandriscoll