[Zope] Linking two tables

[Thomas B. Passin]

-----Original Message-----
From: Anders Gaarsdal Holst <anders@colorweb.dk>

>
>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).
>
>

You should do this in the query to the database, not in Zope.  You
should make a third table that relates employees to dvds. Then, using
SQL, you would use a query something like this:

select employee_name, dvd_name
from employee_dvd, employee.dvd
where employee.employee_id=employee_dvd.employee_id
    and dvd.dvd_id=employee_dvd.dvd_id
order by employee_name

If the database supports joins you can use joins in the from statement
and omit the where statement.

Then you would use Zope to display the results.

Tom Passin