Re: concepts?
От | |
---|---|
Тема | Re: concepts? |
Дата | |
Msg-id | 20050517164645.80837.qmail@web52405.mail.yahoo.com обсуждение исходный текст |
Ответ на | concepts? (D.C. <coughlandesmond@yahoo.fr>) |
Список | pgsql-novice |
> OK, now this is one concept I need explaining: do I > need to set up > 'links' from one table to another when I create the > tables? yes, if the tables relate to each other in some manner and you want to express it in your db. Here is > the 'clients' table of a new db that I've created > ... > > \d clients > Table "public.clients" > Column | Type | > Modifiers > ------------------+-----------------------+----------- > client_id | integer | not > null > nom | character varying(25) | not > null > prenom | character varying(25) | not > null > dob | date | > courriel | character varying(25) | not > null > numero_rue | numeric | not > null > nom_rue | character varying(50) | not > null > cp | character varying(6) | not > null > ville | character varying(25) | not > null > pays | character varying(10) | not > null > telephone | character varying(15) | not > null > date_inscription | date | > dernier_achat | date | > Indexes: > "clients_pkey" PRIMARY KEY, btree (client_id) > Check constraints: > "clients_check" CHECK (dernier_achat > > date_inscription) > > As you can see, there is a primary key which is the > client_id. Now the > 'purchases' table.. > > \d ventes > Table "public.ventes" > Column | Type | Modifiers > --------------+---------+----------- > vente_number | integer | not null > titre_id | integer | not null > prix_vente | numeric | not null > vendu_a | integer | not null > vendu_le | date | not null > Indexes: > "ventes_pkey" PRIMARY KEY, btree (vente_number) > Check constraints: > "ventes_vendu_le_check" CHECK (vendu_le > > '2005-12-31'::date) > Foreign-key constraints: > "ventes_titre_id_fkey" FOREIGN KEY (titre_id) > REFERENCES > stock(stock_id) > "ventes_vendu_a_fkey" FOREIGN KEY (vendu_a) > REFERENCES > clients(client_id) > > Now I have a key 'vendu_a' (which is French for > 'sold to') which links > to client_id in the clients table. Is this what has > to be done every > time I want to link, or can I use the where clause ? > > Thanks. > > D. DC, i *highly* recommend you get pgAdminIII or PHPpgAdmin. the best way to resolve these kinds of issues is to create sql and run it against your test data - especially when you are new. like i am. ;-) i thought you were joining three tables. you only listed two here. i'll give you a query i created to link three tables. SELECT notes.assembly_notes FROM t_product prod, t_link_product_assembly_notes link, t_assembly_notes notes WHERE link.product_id = 2 AND link.product_id = prod.product_id AND link.assembly_notes_id = notes.assembly_notes_id; i have three tables - one ids products, another ids notes and the third table links a note id to a product id. this allows me to link a single note to multiple products. i have three where clauses because they are all required to eliminate the repeating data you are now seeing. i wouldn't have figured this out except through trial and error inputting sql against my data. i'm not that sql smart yet. 1. my product id has to be 2 (in practice, this will be a variable whose value is taken from an application). 2. the product_id in the product table has to match the product_id in the link table, otherwise, i get notes unrealted to the product. 3. the assembly_notes_id has to match in the assembly notes table and the link table or else you will see repeating entries. for example, if i have three notes (ids 1,2,3) assigned to one product (ids 1), i will have three entries in assembly notes and three entries in my link table product 1 product 1 assembly notes 1 note 1 2 note 2 3 note 3 link 1 1 1 2 1 3 if i leave out where clause #3 (equality of assembly note ids in assembly note table and link table), i get the following cartesian result. an link 1 1 1 2 1 3 2 1 2 2 2 3 3 1 3 2 3 3 by setting the equality rquirement, i'm left with what i want.... 1 1 2 2 3 3 again, trial and error will help you get the feel for what you need to know. get the ability to test sql against your db and compare the result with your needs. you may want to google some sql tutorials. heck, i will be doing that shortly since i'm a rookie, myself. i have a couple books on the subject, too. best of luck. Discover Yahoo! Use Yahoo! to plan a weekend, have fun online and more. Check it out! http://discover.yahoo.com/
В списке pgsql-novice по дате отправления:
Следующее
От: Scott MarloweДата:
Сообщение: Re: [JDBC] Using PgSQL in high volume and throughput problem