pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMultiple tables query on Mapserver
5+ messages / 2 participants
[nested] [flat]
* Multiple tables query on Mapserver
@ 2012-02-02 05:16 tiplip <chengwei02@gmail.com>
2012-02-02 05:22 ` Re: Multiple tables query on Mapserver David Johnston <polobo@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: tiplip @ 2012-02-02 05:16 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Multiple tables query on Mapserver
2012-02-02 05:16 Multiple tables query on Mapserver tiplip <chengwei02@gmail.com>
@ 2012-02-02 05:22 ` David Johnston <polobo@yahoo.com>
2012-02-02 05:32 ` Re: Multiple tables query on Mapserver tiplip <chengwei02@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: David Johnston @ 2012-02-02 05:22 UTC (permalink / raw)
To: tiplip <chengwei02@gmail.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Multiple tables query on Mapserver
2012-02-02 05:16 Multiple tables query on Mapserver tiplip <chengwei02@gmail.com>
2012-02-02 05:22 ` Re: Multiple tables query on Mapserver David Johnston <polobo@yahoo.com>
@ 2012-02-02 05:32 ` tiplip <chengwei02@gmail.com>
2012-02-02 05:45 ` Re: Multiple tables query on Mapserver David Johnston <polobo@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: tiplip @ 2012-02-02 05:32 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Multiple tables query on Mapserver
2012-02-02 05:16 Multiple tables query on Mapserver tiplip <chengwei02@gmail.com>
2012-02-02 05:22 ` Re: Multiple tables query on Mapserver David Johnston <polobo@yahoo.com>
2012-02-02 05:32 ` Re: Multiple tables query on Mapserver tiplip <chengwei02@gmail.com>
@ 2012-02-02 05:45 ` David Johnston <polobo@yahoo.com>
2012-02-02 07:58 ` Re: Multiple tables query on Mapserver tiplip <chengwei02@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: David Johnston @ 2012-02-02 05:45 UTC (permalink / raw)
To: tiplip <chengwei02@gmail.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Multiple tables query on Mapserver
2012-02-02 05:16 Multiple tables query on Mapserver tiplip <chengwei02@gmail.com>
2012-02-02 05:22 ` Re: Multiple tables query on Mapserver David Johnston <polobo@yahoo.com>
2012-02-02 05:32 ` Re: Multiple tables query on Mapserver tiplip <chengwei02@gmail.com>
2012-02-02 05:45 ` Re: Multiple tables query on Mapserver David Johnston <polobo@yahoo.com>
@ 2012-02-02 07:58 ` tiplip <chengwei02@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: tiplip @ 2012-02-02 07:58 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2012-02-02 07:58 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-02-02 05:16 Multiple tables query on Mapserver tiplip <chengwei02@gmail.com>
2012-02-02 05:22 ` David Johnston <polobo@yahoo.com>
2012-02-02 05:32 ` tiplip <chengwei02@gmail.com>
2012-02-02 05:45 ` David Johnston <polobo@yahoo.com>
2012-02-02 07:58 ` tiplip <chengwei02@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox