pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSELECT on a to-be-determined table
2+ messages / 2 participants
[nested] [flat]
* SELECT on a to-be-determined table
@ 2006-02-14 15:28 garhone <cacheung@consumercontact.com>
2006-02-20 11:02 ` Re: SELECT on a to-be-determined table Patrick JACQUOT <patrick.jacquot@anpe.fr>
0 siblings, 1 reply; 2+ messages in thread
From: garhone @ 2006-02-14 15:28 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: SELECT on a to-be-determined table
2006-02-14 15:28 SELECT on a to-be-determined table garhone <cacheung@consumercontact.com>
@ 2006-02-20 11:02 ` Patrick JACQUOT <patrick.jacquot@anpe.fr>
0 siblings, 0 replies; 2+ messages in thread
From: Patrick JACQUOT @ 2006-02-20 11:02 UTC (permalink / raw)
To: pgsql-sql
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)
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-02-20 11:02 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-14 15:28 SELECT on a to-be-determined table garhone <cacheung@consumercontact.com>
2006-02-20 11:02 ` Patrick JACQUOT <patrick.jacquot@anpe.fr>
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