From: | Raymond O'Donnell <rod(at)iol(dot)ie> |
---|---|
To: | Killian Driscoll <killiandriscoll(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Querying same lookup table with multiple columns based on another view |
Date: | 2015-11-17 15:27:06 |
Message-ID: | 564B474A.6060905@iol.ie |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 17/11/2015 15:10, Killian Driscoll 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?
Dunno about efficient, but you're going to need to type out your column
names - I'm only guessing without seeing the DDL, but it sounds as if a
column called macro_lookup_id exists in more than one table.
You'll need to do something like this:
select
sample.macro_lookup_id as col_name_1,
macroscopic.macro_lookup_id as col_name_2,
[....]
This is how you specify column aliases.
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie
From | Date | Subject | |
---|---|---|---|
Next Message | Raymond O'Donnell | 2015-11-17 15:58:00 | Re: Querying same lookup table with multiple columns based on another view |
Previous Message | Melvin Davidson | 2015-11-17 15:21:34 | Re: Querying same lookup table with multiple columns based on another view |