[Zope] Linking two tables

[Martijn Pieters]

At 14:49 19/11/99 , Anders Gaarsdal Holst wrote:

>This question might be slightly off topic. But anywhere here it is:
>
>I have two tables in my MS Access database:
>1. Containg a record for each employee
>
>2. DVD database containing a record for each DVD
>
>What I would like to do with Zope, is to somehow link these two
>together, so that I can write on my intranet that  Mr. X has DVD this
>and that.
>
>My problem is that I can offcause make a field in the first table
>(employee table), where I list the IDnumbers of the DVDs (seperated by
>commas), which the employee owns, but then a query returns these id's
>comma seperated and I don't how to handle this (in a <dtml-in> -loop).

This is a default exercise of database design. You make a third table, 
named employee_dvd maybe, with two columns: employee ID and DVD ID. These 
two columns are the primary key.

You can now use Access' SQL building tools to make a joining select 
statement, along the lines of:

select dvd.*
from dvd, employee_dvd
where
   <dtml-sqltest id column_name="employee_dvd.employee_id"> and
   employee_dvd.dvd_id = dvd.id

This will return all DVD's for a given employee ID. You can use similar 
queries to return all Employees that own a certain DVD.

--
Martijn Pieters, Web Developer
| Antraciet http://www.antraciet.nl
| Tel: +31-35-7502100 Fax: +31-35-7502111
| mailto:mj@antraciet.nl http://www.antraciet.nl/~mj
| PGP: http://wwwkeys.nl.pgp.net:11371/pks/lookup?op=get&search=0xA8A32149
------------------------------------------