[SQL] 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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Multiple tables query on Mapserver
On Feb 2, 2012, at 0:16, tiplip 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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Multiple tables query on Mapserver
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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Multiple tables query on Mapserver
On Feb 2, 2012, at 0:32, tiplip 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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Multiple tables query on Mapserver
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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
