Thread: problems with this wiew

problems with this wiew

From
Manuel Trujillo
Date:
Hi.

I've got a problem with this "wiew":

select gallery_id, multimedia_id
from (view_multimedia_file A inner join ( select  gallery_id,                      multimedia_id,
element_type_id,                     result_type_id,                      subevent_id,
subevent_type_id,                     event_id,                      championship_id,
championship_type_id,                     season_id,                      c_language_id as language_id
 
              from  ( view_gallery B                       inner join ( select gallery_id as c_gallery_id,
                            result_type_id,                                           subevent_id,
                    subevent_type_id,                                           event_id,
           championship_id,                                           championship_type_id,
             season_id,                                           language_id as c_language_id
         from view_gallery_result_type                                  ) C                      on (B.gallery_id =
C.c_gallery_id)                   ) D
 
            ) E

on ( (A.mm_id = E.multimedia_id)  )

);


select *
from (view_multimedia_file A inner join ( select  gallery_id,                      multimedia_id,
element_type_id,                     result_type_id,                      subevent_id,
subevent_type_id,                     event_id,                      championship_id,
championship_type_id,                     season_id,                      c_language_id as language_id
 
              from  ( view_gallery B                       inner join ( select gallery_id as c_gallery_id,
                            result_type_id,                                           subevent_id,
                    subevent_type_id,                                           event_id,
           championship_id,                                           championship_type_id,
             season_id,                                           language_id as c_language_id
         from view_gallery_result_type                                  ) C                      on (B.gallery_id =
C.c_gallery_id)                   ) D
 
            ) E

on ( (A.mm_id = E.multimedia_id)  )

);

The postgresql show error.
Anybody can help me, please??

Thank you very much.

Have a nice day ;-)
TooManySecrets

-- 
Manuel Trujillo         manueltrujillo@dorna.es
Technical Engineer      http://www.motograndprix.com
Dorna Sports S.L.       +34 93 4702864


Re: problems with this wiew

From
Stephan Szabo
Date:
On Tue, 4 Dec 2001, Manuel Trujillo wrote:

> Hi.
>
> I've got a problem with this "wiew":

I'm probably missing something obvious, but
in any case the schema for tables and views
involved (preferably in create statements),
your PostgresQL version and the actual error
message you're getting would help us.




Re: problems with this wiew

From
"Aasmund Midttun Godal"
Date:
Well if you have spelled view "wiew" - that may be part of your problem,

sincerely,

Aasmund.
On Tue, 4 Dec 2001 10:07:17 -0800 (PST), Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
> On Tue, 4 Dec 2001, Manuel Trujillo wrote:
> 
> 
> I'm probably missing something obvious, but
> in any case the schema for tables and views
> involved (preferably in create statements),
> your PostgresQL version and the actual error
> message you're getting would help us.
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org

Aasmund Midttun Godal

aasmund@godal.com - http://www.godal.com/
+47 40 45 20 46


Re: problems with this wiew

From
Masaru Sugawara
Date:
On Tue, 04 Dec 2001 22:31:21 GMT
"Aasmund Midttun Godal" <postgresql@envisity.com> wrote:


> Well if you have spelled view "wiew" - that may be part of your problem,
> 
> sincerely,
> 
> Aasmund.
> On Tue, 4 Dec 2001 10:07:17 -0800 (PST), Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
> > On Tue, 4 Dec 2001, Manuel Trujillo wrote:
> > 
> > 
> > I'm probably missing something obvious, but
> > in any case the schema for tables and views
> > involved (preferably in create statements),
> > your PostgresQL version and the actual error
> > message you're getting would help us.
> > 
> Aasmund Midttun Godal
> 
> aasmund@godal.com - http://www.godal.com/
> +47 40 45 20 46
> 


I would think his problem is cased by how to use aliases, at least, D is 
apparently unnecessary. Since I don't know the definitions of his tables,
I can't affirm what type of an error happened. But if his tables are
correctly defined, his error is probably:   ERROR:  Column reference "***" is ambiguous.

If so, the vague columns reference of his query ought to be adjusted
with explicitly using aliases: B, C, and E (e.g. see below). 


select E.gallery_id, E.multimedia_id from view_multimedia_file A      inner join (select B.gallery_id,
      B.multimedia_id,                         B.element_type_id,                         C.result_type_id,
           C.subevent_id,                         C.subevent_type_id,                         C.event_id,
         C.championship_id,                         C.championship_type_id,                         C.season_id,
                C.c_language_id as language_id                    from view_gallery B                         inner
join(select gallery_id as c_gallery_id,                                            result_type_id,
                     subevent_id,                                            subevent_type_id,
                 event_id,                                            championship_id,
         championship_type_id,                                            season_id,
       language_id as c_language_id                                       from view_gallery_result_type
                   ) C                          on (B.gallery_id = C.c_gallery_id)                ) E       on (A.mm_id
=E.multimedia_id)
 
;


Regards,
Masaru Sugawara




Re: problems with this wiew

From
Masaru Sugawara
Date:
On Tue, 04 Dec 2001 22:31:21 GMT
"Aasmund Midttun Godal" <postgresql@envisity.com> wrote:


> Well if you have spelled view "wiew" - that may be part of your problem,
> 
> sincerely,
> 
> Aasmund.
> On Tue, 4 Dec 2001 10:07:17 -0800 (PST), Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
> > On Tue, 4 Dec 2001, Manuel Trujillo wrote:
> > 
> > 
> > I'm probably missing something obvious, but
> > in any case the schema for tables and views
> > involved (preferably in create statements),
> > your PostgresQL version and the actual error
> > message you're getting would help us.
> > 
> Aasmund Midttun Godal
> 
> aasmund@godal.com - http://www.godal.com/
> +47 40 45 20 46
> 


I would think his problem is caused by how to use aliases, at least, 
D is apparently unnecessary. Since I don't know the definitions of his
tables, I can't affirm what type of an error happened. But if his tables
are correctly defined, his error is probably:   ERROR:  Column reference "***" is ambiguous.

If so, the vague columns reference of his query ought to be adjusted
with explicitly using aliases: B, C, and E (e.g. see below). 


select E.gallery_id, E.multimedia_id from view_multimedia_file A      inner join (select B.gallery_id,
      B.multimedia_id,                         B.element_type_id,                         C.result_type_id,
           C.subevent_id,                         C.subevent_type_id,                         C.event_id,
         C.championship_id,                         C.championship_type_id,                         C.season_id,
                C.c_language_id as language_id                    from view_gallery B                         inner
join(select gallery_id as c_gallery_id,                                            result_type_id,
                     subevent_id,                                            subevent_type_id,
                 event_id,                                            championship_id,
         championship_type_id,                                            season_id,
       language_id as c_language_id                                       from view_gallery_result_type
                   ) C                          on (B.gallery_id = C.c_gallery_id)                ) E       on (A.mm_id
=E.multimedia_id)
 
;


Regards,
Masaru Sugawara