Skip site navigation (1) Skip section navigation (2)

Fw: Selecting Fields in Union in Subquery

From: Tom Burns <tramseyburns(at)yahoo(dot)com>
To: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Fw: Selecting Fields in Union in Subquery
Date: 2012-07-16 10:30:28
Message-ID: 1342434628.60341.YahooMailNeo@web122501.mail.ne1.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
Bartosz,


Thank you so much!

That is the information I needed.

   select "Id","Country","State/Provence"
    from
    (select g.geo_id as "Id", g.geo_name as "Country", gTo.geo_name as "State/Provence"
    from geo g,geo gTo, geo_assoc ga
    where ga.geo_id = g.geo_id
    and ga.geo_id_to = gTo.geo_id
    and g.geo_id IN (select geo_id from geo where geo_type_id='COUNTRY')
    and g.geo_id = 'USA'
    union
    select g.geo_id as "Id", g.geo_name as "Country", '' as
 "State/Provence"
    from geo g LEFT OUTER JOIN geo_assoc ga on g.geo_id = ga.geo_id
    where g.geo_type_id='COUNTRY' and ga.geo_id is null) as GeoList
    where "Id" ='USA'
    order by "State/Provence"; 


or alternate without camel case
    select id,country,state
    from
    (select g.geo_id as "id", g.geo_name as "country", gTo.geo_name as "state"
    from geo g,geo gTo, geo_assoc ga
    where ga.geo_id = g.geo_id
    and ga.geo_id_to = gTo.geo_id
    and g.geo_id IN (select geo_id from geo where geo_type_id='COUNTRY')
    union
    select g.geo_id as "id", g.geo_name as "country", '' as "state"
    from geo g LEFT OUTER JOIN geo_assoc ga on g.geo_id = ga.geo_id
    where g.geo_type_id='COUNTRY' and ga.geo_id is null) as GeoList
    where "id" ='USA'
    order by state;


both work


Again my thanks,

Tom




________________________________
 From: Bartosz Dmytrak <bdmytrak(at)gmail(dot)com>
To: Tom Burns <tramseyburns(at)yahoo(dot)com> 
Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org> 
Sent: Sunday, July 15, 2012 2:41 PM
Subject: Re: [NOVICE] Selecting Fields in Union in Subquery
 


2012/7/13 Tom Burns <tramseyburns(at)yahoo(dot)com>


>
>I can no figure out the syntax for directly addressing fields (for example 'order by StateProvence" or "order by gTo.geo_name" instead of  order by 3)

This should work ("StateProvence" not gTo.geo_name), but You should remember to use " (quotation mark) when You use camel cased names. If not PG will treat fields names like written in small letters. 
You should do something like this:
order by "StateProvence"
not
order by StateProvence
 
I would like to be able to filter and sort the sub-query in the parent query.
I don't get it, I assume You would like to sort/filter sub-query result in parent query.  Then You should remember to use names of sub-query result columns i.e
gTo.geo_name is not visilbe, but geolist."StateProvence" is visible outside sub query

Regards,
Bartek

In response to

pgsql-novice by date

Next:From: Matt MusgroveDate: 2012-07-16 16:38:43
Subject: Smallint needs explicit cast in psql?
Previous:From: Ioannis AnagnostopoulosDate: 2012-07-16 10:24:27
Subject: Re: Index slow down insertions...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group