[SQL] Multiple tables query on Mapserver

[tiplip]

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

[tiplip]

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

[tiplip]

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