First, you are describing a "one-to-many" relationship, bceasue one "file" record can be referenced by many "people" records. If this is the case, you may wish to re-design your tablse such that a "people record" contains a "file_id" field. You can then do away with the "filespeople" table altogether. Normalization is a good thing; but not when it is at the detriment of good design including how one processes it.
If you do require the "filespeople" table, then you'll have to INSERT records programmatically with your favorite scripting (PHP,ASP,PERL) language or program language. FYI. Your "filespeople" file indicates that both fields are PRIMARY keys. That cannot be. Only one field may be PRIMARY. I just now tried it to be certain. -----Original Message----- From: Erik Price [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 09, 2002 11:14 AM To: [EMAIL PROTECTED] Cc: Etienne Marcotte Subject: INSERTing into joined tables? Hello, everyone -- I have received a great deal of help from many members of this list, so I'd like to acknowledge that now. Someday, when I have my head wrapped around this stuff, I hope to return the favor. In the meantime, however, I have come across my worst brainbuster yet. Any help on this is greatly appreciated. I constructed my tables in the most "normalized" way that I could (without overdoing it), so that in some cases, there is no -direct- relationship between tables. That is, I have a table called "people" and a table called "files", and a foreign key table called "filespeople": Database: medialab_db Table: people +-------------+----------------------+------+-----+ | Field | Type | Null | Key | +-------------+----------------------+------+-----+ | people_id | smallint(5) unsigned | | PRI | (auto-incremented) | first_name | varchar(36) | | | | last_name | varchar(36) | | | +-------------+----------------------+------+-----+ Database: medialab_db Table: files +-------------+-----------------------+------+-----+ | Field | Type | Null | Key | +-------------+-----------------------+------+-----+ | file_id | mediumint(8) unsigned | | PRI | (auto-incremented) | file_name | varchar(64) | | | +-------------+-----------------------+------+-----+ Database: medialab_db Table: filespeople +-----------+-----------------------+------+-----+ | Field | Type | Null | Key | +-----------+-----------------------+------+-----+ | file_id | mediumint(8) unsigned | | PRI | | people_id | smallint(5) unsigned | | PRI | +-----------+-----------------------+------+-----+ The relationship, in real life, is that I would like to establish many-to-many relationships between "files" records and "people" records, so that a record in "files" would be associated with several people from "people". There is a foreign key table called "filespeople". The SQL used to write a SELECT statement would use the join like so: SELECT files.file_name, people.first_name, people.last_name FROM files, people, filespeople WHERE files.file_name = $filename AND files.file_id = filespeople.file_id AND people.people_id = filespeople.people_id (the $filename variable is a user-selected variable, I'm using PHP) So I designed my "files" and "people" tables without any direct relationship with one another, thinking to link them with the SELECT statement. What I completely forgot, up until this point, was that I would need to INSERT these records (from pre-written HTML/PHP forms), and there is no WHERE clause in the INSERT statement to keep everything together. In my scenario, a user might add a record to "files" and wish to associate that record to some of the records in "people", either new or pre-existing (typed into an HTML text input form or something). How should SQL code be arranged to "link" these records over the foreign key table? INSERT INTO files (file_name) VALUES ("$filename") and INSERT INTO people (first_name, last_name) VALUES ("$firstname", "$lastname") but... to keep it all together... is lost on me... and then later to have UPDATE statements to do the same thing! Although I suspect this may be easier as I can use the WHERE clause in an UPDATE statement. If anyone has a link to a tutorial on this very concept, that would be greatly appreciated as well! Thank you, Erik --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
