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

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 <
killiandriscoll(at)gmail(dot)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.