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

[Melvin Davidson <melvin6925@xxxxxxxxx>]

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@xxxxxxxxx> 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
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.