Re: concepts?

> 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/