Re: Multiple tables query on Mapserver - Mailing list 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.