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). Regards, A n d e r s G a a r s d a l H o l s t
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 ------------------------------------------
----- Original Message ----- From: Anders Gaarsdal Holst <anders@colorweb.dk> To: <zope@zope.org> Sent: Friday, November 19, 1999 2:49 PM Subject: [Zope] Linking two tables
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 not too nice solution. It would be better if you cretaed a third table(let's say owned) with two fields: -employeeid -dvdid Then with the next select you can query the dvds owned by an employee: select dvds.* from owned, dvds where dvds.id=owned.dvdid and owned.employeeid=<id of the given employee> In Zope try to add a Z Search Interface. It generates the basic documents for you. You can customize them if it is not enough for you. Arpad Kiss
participants (3)
-
Anders Gaarsdal Holst -
Arpad Kiss -
Martijn Pieters