Обсуждение: Multiple tables query on Mapserver
Hi all, I have a couple of tables with same structure but standing for different layers(1,2,3...) respectivle for Mapserver, table1 gid | id | name | address | post code | layer | geom -----------+------+----------+-------------+-----------+--------+------------ 1 1 'name11' 'address11' 102356 1 geom11 2 2 'name12' 'address12' 102356 1 geom12 - - 'name1-' 'address1-' 102356 1 geom1- table2 gid | id | name | address | post code | layer | geom -----------+------+----------+-------------+-----------+--------+------------ 1 1 'name21' 'address21' 102356 2 geom21 2 2 'name22' 'address22' 102356 2 geom22 - - 'name2-' 'address2-' 102356 2 geom2- table3 gid | id | name | address | post code | layer | geom -----------+------+----------+-------------+-----------+--------+------------ 1 1 'name31' 'address31' 102356 3 geom31 2 2 'name32' 'address32' 102356 3 geom32 - - 'name3-' 'address3-' 102356 3 geom3- I want to get query results from table1, 2, 3... if key word matches, say name like 'name' as follows: input 'name', results will be like table2 gid | id | name | address | post code | layer | geom -----------+------+----------+-------------+-----------+---------+------------ 1 1 'name11' 'address11' 102356 1 geom11 2 2 'name12' 'address12' 102356 1 geom12 3 3 'name21' 'address21' 102356 2 geom21 4 4 'name22' 'address22' 102356 2 geom22 5 5 'name31' 'address31' 102356 3 geom31 6 6 'name32' 'address32' 102356 3 geom32 - - --------- --------- ------- - - can I achieve that results with a single query sentence? and how? any good ideas will be appricated? thanks -- View this message in context: http://postgresql.1045698.n5.nabble.com/Multiple-tables-query-on-Mapserver-tp5449642p5449642.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
On Feb 2, 2012, at 0:16, tiplip <chengwei02@gmail.com> wrote: > Hi all, > > I have a couple of tables with same structure but standing for different > layers(1,2,3...) respectivle for Mapserver, > > table1 > gid | id | name | address | post code | layer > | geom > -----------+------+----------+-------------+-----------+--------+------------ > 1 1 'name11' 'address11' 102356 1 > geom11 > 2 2 'name12' 'address12' 102356 1 > geom12 > - - 'name1-' 'address1-' 102356 > 1 geom1- > > table2 > gid | id | name | address | post code | layer > | geom > -----------+------+----------+-------------+-----------+--------+------------ > 1 1 'name21' 'address21' 102356 2 > geom21 > 2 2 'name22' 'address22' 102356 2 > geom22 > - - 'name2-' 'address2-' 102356 2 > geom2- > > > table3 > gid | id | name | address | post code | layer > | geom > -----------+------+----------+-------------+-----------+--------+------------ > 1 1 'name31' 'address31' 102356 3 > geom31 > 2 2 'name32' 'address32' 102356 3 > geom32 > - - 'name3-' 'address3-' 102356 > 3 geom3- > > > I want to get query results from table1, 2, 3... if key word matches, say > name like 'name' as follows: > > input 'name', results will be like > > table2 > gid | id | name | address | post code | layer > | geom > -----------+------+----------+-------------+-----------+---------+------------ > 1 1 'name11' 'address11' 102356 1 > geom11 > 2 2 'name12' 'address12' 102356 1 > geom12 > 3 3 'name21' 'address21' 102356 2 > geom21 > 4 4 'name22' 'address22' 102356 2 > geom22 > 5 5 'name31' 'address31' 102356 3 > geom31 > 6 6 'name32' 'address32' 102356 3 > geom32 > - - --------- --------- ------- - > - > > > can I achieve that results with a single query sentence? and how? > any good ideas will be appricated? > > thanks > > The general method is to use "UNION" but not sure about the changing of the gid and id values... David J.
I just need gid or id in increasing order start from 0 (or 1), fetching their original gid (or id) value is not necessary:) can I do that? David Johnston wrote > > > The general method is to use "UNION" but not sure about the changing of > the gid and id values... > > David J. > -- > Sent via pgsql-sql mailing list (pgsql-sql@) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- View this message in context: http://postgresql.1045698.n5.nabble.com/Multiple-tables-query-on-Mapserver-tp5449642p5449662.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
On Feb 2, 2012, at 0:32, tiplip <chengwei02@gmail.com> wrote: > I just need gid or id in increasing order start from 0 (or 1), fetching their > original gid (or id) value is not necessary:) > can I do that? > > > David Johnston wrote >> >> >> The general method is to use "UNION" but not sure about the changing of >> the gid and id values... >> >> David J. >> -- >> Window function Row_number() over () David J.
David Johnston wrote > > > Window function > > Row_number() over () > > David J. > > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > Thanks, David It really works. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Multiple-tables-query-on-Mapserver-tp5449642p5449933.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.