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

Re: Multiple tables query on Mapserver

From: David Johnston <polobo(at)yahoo(dot)com>
To: tiplip <chengwei02(at)gmail(dot)com>
Cc: "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Multiple tables query on Mapserver
Date: 2012-02-02 05:22:46
Message-ID: 59D2E8F6-962D-4DDC-96C0-5985AE0F4D9A@yahoo.com (view raw or flat)
Thread:
Lists: pgsql-sql
On Feb 2, 2012, at 0:16, tiplip <chengwei02(at)gmail(dot)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.

In response to

Responses

pgsql-sql by date

Next:From: tiplipDate: 2012-02-02 05:32:04
Subject: Re: Multiple tables query on Mapserver
Previous:From: tiplipDate: 2012-02-02 05:16:53
Subject: Multiple tables query on Mapserver

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