Обсуждение: SELECT on a to-be-determined table

Hi,

I'm a new at this. So please forgive if I mess up. Also, if there is
already a reference/tutorial somewhere, feel free to point me to it.

Here's my situation:
db=# select * from projects;projid | projname
--------+----------     1 | cars     2 | houses     3 | pets
(3 rows)

db=# select * from cars;carid | carname
-------+---------    1 | ford    2 | mazda
(2 rows)

db=# select * from houses;houseid | housename
---------+-----------      1 | semi      2 | trailer      3 | mansion
(3 rows)

db=# select * from pets;petid | petname
-------+---------    1 | dog    2 | cat    3 | bird
(3 rows)

Is it possible to do this:
Give me all the rows of the table whose project id is 2 (or whatever
projid).

Thanks

garhone wrote:

>Hi,
>
>I'm a new at this. So please forgive if I mess up. Also, if there is
>already a reference/tutorial somewhere, feel free to point me to it.
>
>Here's my situation:
>db=# select * from projects;
> projid | projname
>--------+----------
>      1 | cars
>      2 | houses
>      3 | pets
>(3 rows)
>
>db=# select * from cars;
> carid | carname
>-------+---------
>     1 | ford
>     2 | mazda
>(2 rows)
>
>db=# select * from houses;
> houseid | housename
>---------+-----------
>       1 | semi
>       2 | trailer
>       3 | mansion
>(3 rows)
>
>db=# select * from pets;
> petid | petname
>-------+---------
>     1 | dog
>     2 | cat
>     3 | bird
>(3 rows)
>
>Is it possible to do this:
>Give me all the rows of the table whose project id is 2 (or whatever
>projid).
>
>Thanks
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org
>
>
Your way of thinking leads to the need of comparing a field to a table name.
Such a request requires two steps
1 - retrieve the name of the table to search in, store it in a variable
2 - use execute to issue a request to that table.

Instead, I think it would be better to use only two tables:
1 - projects (projid, projname)
2 - items (itemid, projid, itemproperty1,itemidproperty2,...)
You would have in the second table, to take your example:
projid | itemid | itemname               |    1 |      1 | ford                   |    1 |      2 | mazda
  |    2 |      1 | semi                   |    2 |      2 | trailer                |    2 |      3 | mansion
    |    3 |      1 | dog                    |    3 |      2 | cat                    |    3 |      3 | bird
      |
 
Your request would become :
SELECT itemid, itemname FROM items where projid=2

The problem of having a different set of properties
for the items of differents projects could be solved with three tables:
project(projid, projname)
itempropertymeaning(projid, propid, propmeaning)
itemproperty(projid, itemid, propid, propvalue)